June 6, 2012 at 1:09 am
Hi All,
I have a scenario where in i have to convert rows to columns dynamically, im trying but not able to get the exact result
The data is as follows
cusidnameemail
111abcabc@xyz.com
222aaaaaa@xyz.com
222aaaaaa1@xyz.com
222aaaaaa2@xyz.com
222aaaaaa3@xyz.com
333bbbbbb@xyz.com
333bbbbbb1@xyz.com
I need to get the output as follows
cusidnameemail1email2email3 email4
111abcabc@xyz.comnullnullnull
222aaaaaa@xyz.comaaa1@xyz.comaaa2@xyz.comaaa3@xyz.com
333bbbbbb@xyz.combbb1@xyz.comnullnull
Thanks
June 6, 2012 at 1:13 am
Take a look in my signature for the Cross Tabs links by Jeff, the second part is in relation to dynamic cross tabs.
June 6, 2012 at 3:36 am
Here are a few versions of doing it:
--Creating Table
Create Table Ex
(cusid int,
name varchar(3),
email varchar(20) )
--Inserting Sample Data
Insert Into Ex
Select 111,'abc','abc@xyz.com'
union ALL
Select 222,'aaa','aaa@xyz.com'
union ALL
Select 222,'aaa','aaa1@xyz.com'
union ALL
Select 222,'aaa','aaa2@xyz.com'
union ALL
Select 222,'aaa','aaa3@xyz.com'
union ALL
Select 333,'bbb','bbb@xyz.com'
union ALL
Select 333,'bbb','bbb1@xyz.com'
--Query Using Case
Select cusid, MAX(Name),
Max(Case When rn = 1 Then email Else '' End) As Email1,
Max(Case When rn = 2 Then email Else '' End) As Email2,
Max(Case When rn = 3 Then email Else '' End) As Email3,
Max(Case When rn = 4 Then email Else '' End) As Email4
From
(Select *, ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As rn From Ex) As a
Group By cusid
--Static Pivot
Select cusid, name, [Email1], [Email2] , [Email3] , [Email4]
From
(Select *, 'Email' + Cast(ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As varchar) As rn From Ex) As a
Pivot
(max(Email) For rn IN ([Email1],[Email2],[Email3],[Email4])) As pvt
Order By cusid
--Dynamic Pivot
Declare @cols varchar(max), @sql varchar(max)
Declare @temp Table(Cols varchar(max))
Insert Into @temp
Select Distinct 'Email' + Cast(ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As varchar) As rn From Ex
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(cols) From @temp
Set @sql = 'Select cusid, name, '+@cols+'
From
(Select *, ''Email'' + Cast(ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As varchar) As rn From Ex) As a
Pivot
(max(Email) For rn IN ('+@cols+')) As pvt
Order By cusid'
Execute (@sql)
June 7, 2012 at 12:28 am
Hi,
Had done the same way but did not use "partition by" so was not able to get the exact output
Thanks for the reply... It helped me and solved it 🙂
June 7, 2012 at 5:16 am
manzilkolur23 (6/7/2012)
Hi,Had done the same way but did not use "partition by" so was not able to get the exact output
Thanks for the reply... It helped me and solved it 🙂
You're welcome. I am glad it was helpful for you. 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply