multiple rows convert to multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

multiple rows convert to multiple columns

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-07 : 05:49:12

Dear all
i have a table
my table structure is

sno type typename
1 V llllll
1 O kkkkkk
1 V llll
1 V ll
1 O kkkk

so i want move the multiple rows in multiple columns group by sno

so which way i can get this 5 rows into 5 columns like

sno 1col 2col 3 col 4 col 5 col
1 , llllll, kkkkkk , llll, ll, kkkk

how to fetch this way

anybody know pls help me

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-07 : 06:01:45

You need some column to order by to get the results as given but

;with cte as (select *, seq = row_number() over (partition by sno order by typename))
select sno
, col1 = max(case when seq = 1 then typename else '' end)
, col2 = max(case when seq = 2 then typename else '' end)
, col3 = max(case when seq = 3 then typename else '' end)
, col4 = max(case when seq = 4 then typename else '' end)
, col5 = max(case when seq = 5 then typename else '' end)
from cte
group by sno

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-08 : 06:31:36

You can use a Dynamic Pivot as follows as well:


--Creating Table

Create Table Ex
(sno int,
 type Char(1),
 typename varchar(30) )


--Inserting Sample Data

Insert Into Ex
Select 1, 'V', 'llllll'
Union ALL
Select 1, 'O', 'kkkkkk'
Union ALL
Select 1, 'V', 'llll'
Union ALL
Select 1, 'V', 'll'
Union ALL
Select 1, 'O', 'kkkk'


--Dynamic Pivot

Declare @cols varchar(max), @sql varchar(max), @cols1 varchar(max)
Declare @temp Table(Cols varchar(30))
Insert Into @temp
Select 'COLUMN' + Cast(ROW_NUMBER() Over (Order By (Select (NULL))) As Varchar(max)) As rn From Ex
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @temp
Select @cols1 = Coalesce(@cols1 + '), Max(', '') + QUOTENAME(Cols) From @temp
Select @cols1 = 'Max(' + @cols1 + ')'
Set @sql = 'Select sno, '+@cols1+' From
				(Select sno, '+@cols+' From 
					(Select *, ''COLUMN'' + Cast(ROW_NUMBER() Over (Order By (Select (NULL))) As Varchar(max)) As rn From Ex) As a
					 Pivot
					(max(typename) For rn IN ('+@cols+')) As pvt) As x
			Group By sno'
Execute (@sql)

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources