ROWS TO COLUMNS WITH QUERY - SQL Server Forums

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)

ROWS TO COLUMNS WITH QUERY

Author

Topic

comparin.fabio
Starting Member

3 Posts

Posted - 2012-06-11 : 13:24:44

QUERY CODE:
SELECT [B].[Id],
[I].[SN]
FROM Imballi] I
JOIN ImballiScatole] S ON S.Id = I.IdScatola
JOIN ImballiBancali] B ON B.Id = S.IdBancale
WHERE [S].[Id] = '3986'

RESULT:
ID SN
-- ------------
178 718218
178 718221
178 718217
178 718220
178 718223
178 718224
178 718226
178 718225
178 718219
178 718228
178 718227
178 718229

I need to retrieve all records to one row with a query

RESULT EXAMPLE:
ID SN1 SN2 SN3 SNX
-- ------ ------ ------ ------
178 718218 718221 718217 718220....

Many thanks to everyone!

CF

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-11 : 13:35:46

See here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=175651

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-06-12 : 08:52:30

You can do a Dynamic Pivot to attain the Expected Result as follows:


--Creating table

Create Table Ex
(ID	int,
 SN int )


--Inserting Sample Data
 
Insert into Ex
Select 178,	718218
union ALL
Select 178,	718221	
union ALL
Select 178,	718217	
union ALL
Select 178,	718220	
union ALL
Select 178,	718223	
union ALL
Select 178,	718224	
union ALL
Select 178,	718226	
union ALL
Select 178,	718225	
union ALL
Select 178,	718219	
union ALL
Select 178,	718228
union ALL	
Select 178,	718227	
union ALL
Select 178,	718229


--Dynamic Pivot

Declare @cols varchar(max), @sql Varchar(max)
Select @cols = Coalesce(@cols + ', ', '') +QUOTENAME(rn) From
	(Select *, 'SN' + Cast(ROW_NUMBER() Over (Order By (Select NULL) ) AS Varchar(30) ) As rn From Ex) As a
Set @sql = 'Select Id, '+@cols+' From 
				(Select *, ''SN'' + Cast(ROW_NUMBER() Over (Order By (Select NULL) ) AS Varchar(30) ) As rn From Ex) As a
			Pivot
			(Max(SN) For rn IN ('+@cols+')) As pvt'
Execute (@sql)

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

comparin.fabio
Starting Member

3 Posts

Posted - 2012-06-22 : 13:46:40

Many thanks for help guys!

CF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources