question

fishhbot avatar image
fishhbot asked

Column to Row

Hello, I'm new to SQL and am wondering how/if I can do this... I have a table (Servers) which contains three columns...Name, IP and Volume (name of a server, IP address and the drives on each server) My query is this: select Name, IP, Volume from Servers with (nolock) Output gives me similar to this: ![alt text][1] But what I need is one row per server (similar to the image below)...showing the Name and IP and then all Volumes on the same row. The Volume column will always be an unknown number of entries per server...anywhere from 1 to anything. I have no idea how to do this or if it can be done. ![alt text][2] Essentially want to select the Name and IP and then add any volume that matches the IP to that same row. Thank you in advance for any help/suggestions! [1]: /storage/temp/3938-image1.jpg [2]: /storage/temp/3939-image2.jpg
sqlcolumn
image1.jpg (23.5 KiB)
image2.jpg (18.6 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

awesome! thanks for the help!
0 Likes 0 ·

1 Answer

·
yen89 avatar image
yen89 answered
SELECT [Pivot].[Name], [Pivot].[IP], [1] AS [volume1], [2] AS [volume2], [3] AS [volume3], [4] AS [volume4], [5] AS [volume5] FROM (SELECT Name, IP, volume, ROW_NUMBER()OVER(PARTITION BY [Name] ORDER BY [Volume]) AS RowNm FROM dbo.[Servers] WITH (NOLOCK)) AS [source] PIVOT (MAX(volume) FOR [RowNm] IN ([1], [2], [3], [4], [5])) AS [Pivot] If there are more you can and 6, 7, 8 and so on. I got this information from the following page http://sqlmag.com/t-sql/pivoting-without-aggregation. PIVOT usually uses aggregation so the row_number was the trick as he states '***Because each ordinal number is associated with only one member in each team, it's now possible to use the MAX aggregate function in the PIVOT operation***.' Hope that helps :)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.