March 9, 2012 at 8:47 am
I have the below data in a select statement.
RealKBServerName
KB2518870server1
KB2518870server2
KB2518870server3
KB2518870server4
KB2518870server5
KB2518870server6
KB2518870server7
KB2533523server1
KB2533523server3
KB2533523server4
KB2533523server5
KB2533523server6
KB2533523server7
KB2535512server8
I want it to look like this?
RealKBServerName
KB2518870server1server2server3server4server5server6server7
KB2533523server1server3server4server5server6server7
KB2535512server8
Anyone know how I might "Pivot" this data to get this result? The columns are not set to a particular server just the next one.
Basically instead of repeating the KB, I just want to list all the servers for that KB.
I apologize if this is confusing.
March 9, 2012 at 9:06 am
SELECT DISTINCT
A
,(SELECT t2.B + ' '
FROM (SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS rownum FROM #tbl) t2
WHERE t2.A = T.A
FOR XML PATH('')) AS B
FROM #tbl T
Of course this would be a distinct list..edited
__________________________
Allzu viel ist ungesund...
March 9, 2012 at 11:18 am
Here is my final solution, The one issue I had was it still repeated x number of rows so slapping a DISTINCT solved that. What does the XML do for you here?
SELECT DISTINCT WindowsVersion, RealKB
,(SELECT u2.ServerName + ' '
FROM (SELECT RealKB, ServerName, ROW_NUMBER() OVER (PARTITION BY RealKB ORDER BY RealKB) AS rownum FROM Updates) U2
WHERE U2.REalKB = U.RealKB
FOR XML PATH('')) AS ServerName
FROM Updates U
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply