Values of row in column

  • Hi

    I need the value of row in column corresponding to ID. Below is the Table structure and sample data along with result set required:

    CREATE TABLE lest

    (ID INT,Scheme varchar (5),

    Type SMALLINT)

    GO

    INSERT INTO Test VALUES

    (1,'ATC', 2),

    (1,'IDP', 37),

    (1,'ATC', 8),

    (2,'ATC', 6),

    (2,'MDF' 37),

    (3,'ATC', 23),

    (3,'MDF' 8),

    (3,'IDP' 6),

    (3,'ATC', 6)

    Result Set Required:

    ID Scheme1Type1Scheme2Type2Scheme3Type3Scheme4Type4

    1ATC2IDP37ATC8NULLNULL

    2ATC6MDF37NULLNULLNULLNULL

    3ATC23MDF8IDP6ATC6

    Thanks

  • The way I see it, you have 3 choices:

    1. Dynamic SQL if you need a variable number of columns

    2. A PIVOT (I think) but the max. columns will be fixed

    3. Or the following if you can confirm that the max number of Schemas/Types is 4 (but that could be expanded as much as you need - the key is that the number of columns is fixed):

    DECLARE @test-2 TABLE

    (ID INT,[Schema] varchar (5),

    [Type] SMALLINT)

    INSERT INTO @test-2 (ID, [Schema], [Type])

    SELECT 1,'ATC', 2

    UNION ALL SELECT 1,'IDP', 37

    UNION ALL SELECT 1,'ATC', 8

    UNION ALL SELECT 2,'ATC', 6

    UNION ALL SELECT 2,'MDF', 37

    UNION ALL SELECT 3,'ATC', 23

    UNION ALL SELECT 3,'MDF', 8

    UNION ALL SELECT 3,'IDP', 6

    UNION ALL SELECT 3,'ATC', 6

    ;WITH p AS (

    SELECT ID

    ,CASE WHEN rk=1 THEN [Schema] ELSE NULL END AS Schema1

    ,CASE WHEN rk=1 THEN [Type] ELSE NULL END AS Type1

    ,CASE WHEN rk=2 THEN [Schema] ELSE NULL END AS Schema2

    ,CASE WHEN rk=2 THEN [Type] ELSE NULL END AS Type2

    ,CASE WHEN rk=3 THEN [Schema] ELSE NULL END AS Schema3

    ,CASE WHEN rk=3 THEN [Type] ELSE NULL END AS Type3

    ,CASE WHEN rk=4 THEN [Schema] ELSE NULL END AS Schema4

    ,CASE WHEN rk=4 THEN [Type] ELSE NULL END AS Type4

    FROM (

    SELECT ID, [Schema], [Type]

    ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) As rk

    FROM @test-2) x)

    SELECT ID, MAX(Schema1) AS Schema1, MAX(Type1) As Type1

    , MAX(Schema2) AS Schema2, MAX(Type2) As Type2

    , MAX(Schema3) AS Schema3, MAX(Type3) As Type3

    , MAX(Schema4) AS Schema4, MAX(Type4) As Type4

    FROM p

    GROUP BY ID

    Please advise what works for you.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Check out the PIVOT statement:

    Using PIVOT and UNPIVOT

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • @dwain.c ...Thanks a lot 🙂

  • @dwain.c

    Please guide me for solution through the dynamic SQL if number of column is dynamic(not fixed)..

    Thanks

  • Number of columns in the output set will depend on the maximum of the counts of the IDs in your table. Note that you can't use a TABLE VARIABLE for the dynamic SQL so I switched to using a temp table.

    CREATE TABLE #Test

    (ID INT,[Schema] varchar (5),

    [Type] SMALLINT)

    INSERT INTO #Test (ID, [Schema], [Type])

    SELECT 1,'ATC', 2

    UNION ALL SELECT 1,'IDP', 37

    UNION ALL SELECT 1,'ATC', 8

    UNION ALL SELECT 2,'ATC', 6

    UNION ALL SELECT 2,'MDF', 37

    UNION ALL SELECT 3,'ATC', 23

    UNION ALL SELECT 3,'MDF', 8

    UNION ALL SELECT 3,'IDP', 6

    UNION ALL SELECT 3,'ATC', 6

    DECLARE @cols INT, @SQL VARCHAR(MAX), @SQL1 VARCHAR(MAX), @SQL2 VARCHAR(MAX), @n INT

    SELECT @cols = MAX(CountIDs)

    FROM (SELECT COUNT(ID) As CountIDs FROM #test GROUP BY ID) x

    SELECT @n = 1, @SQL1 = '', @SQL2 = ''

    WHILE @n <= @cols

    SELECT @SQL1 = @SQL1 + ',CASE WHEN rk=' + CAST(@n AS VARCHAR) +

    ' THEN [Schema] ELSE NULL END AS Schema' + CAST(@n AS VARCHAR) +

    ',CASE WHEN rk=' + CAST(@n AS VARCHAR) +

    ' THEN [Type] ELSE NULL END AS Type' + CAST(@n AS VARCHAR)

    ,@SQL2 = @SQL2 + ',MAX(Schema' + CAST(@n AS VARCHAR) + ') AS Schema' + CAST(@n AS VARCHAR) +

    ',MAX(Type' + CAST(@n AS VARCHAR) + ') AS Type' + CAST(@n AS VARCHAR)

    ,@n = @n + 1

    SELECT @SQL = ';WITH p AS (SELECT ID' + @SQL1 +

    ' FROM (' +

    'SELECT ID, [Schema], [Type], ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) As rk FROM #test) x)' +

    ' SELECT ID' + @SQL2 + ' FROM p GROUP BY ID'

    EXEC (@SQL)

    --SELECT @SQL

    DROP TABLE #Test

    The trick to doing this kind of a setup is to construct your query first (non-dynamic) then analyze to identify repeating groups that need to be handled on the WHILE. In this case, there are two (@SQL1 and @SQL2).

    Then build the string and display (SELECT) it to the results pane. From there you can copy it out and try to run it, fixing it as you go along.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thanks 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply