How to achieve?

  • 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.

  • 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...

  • 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