CODEWITHSUNDEEP
sqlsql-server
user1132134
user1132134

Reputation: 27

SQL Query all record in one row

I have a datatable like 

CANDID  QID   OptID
11      56    116
11      56    117
11      57    118
11      57    119
11      60    124
11      60    125
11      60    126
11      62    129
11      62    130
11      62    131

How I can display this data like

CANDID   QID    OptID1   OptID2   OptID3   OptID4 
11       56     116      117      null     null
....
....

11       60     124      125      126      null

I tried this but this is not my desired output.

SELECT CANDID,
       QID,
       MIN(OptID) AS OptID1,
       MAX(OptID) AS OptID4
FROM   #TEMP1
GROUP  BY CANDID,
          QID

Upvotes: 1

Views: 381

Answers (2)

Azeem
Azeem

Reputation: 11

Is this what you are looking for:

DECLARE @cols  AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.OptID) 
            FROM #TEMP1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')        

SET @query = 'SELECT CANDID, QID, ' + @cols + ' from 
            (
                select CANDID
                    , QID
                    , OptID
                from #TEMP1
           ) x
            pivot 
            (
                 AVG(OptID)
                for OptID in (' + @cols + ')
            ) p '

EXECUTE(@query)

Returns

CANDID      QID         116         117         118         119         124         125         126         129         130         131
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
11          56          116         117         NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL
11          57          NULL        NULL        118         119         NULL        NULL        NULL        NULL        NULL        NULL
11          60          NULL        NULL        NULL        NULL        124         125         126         NULL        NULL        NULL
11          62          NULL        NULL        NULL        NULL        NULL        NULL        NULL        129         130         131

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453287

CREATE TABLE #TEMP1
    ([CANDID] int, [QID] int, [OptID] int)
;

INSERT INTO #TEMP1
    ([CANDID], [QID], [OptID])
VALUES
    (11, 56, 116),
    (11, 56, 117),
    (11, 57, 118),
    (11, 57, 119),
    (11, 60, 124),
    (11, 60, 125),
    (11, 60, 126),
    (11, 62, 129),
    (11, 62, 130),
    (11, 62, 131)
;

WITH T
     AS (SELECT [CANDID], 
                [QID], 
                [OptID],
                ROW_NUMBER() OVER (PARTITION BY [CANDID], [QID] ORDER BY [OptID]) AS RN
         FROM   #TEMP1)
SELECT CANDID,
       QID,
       [1] AS OptID1,
       [2] AS OptID2,
       [3] AS OptID3,
       [4] AS OptID4
FROM   T PIVOT (MAX([OptID]) FOR RN IN ([1], [2], [3], [4])) AS P 


DROP TABLE #TEMP1

Returns

CANDID      QID         OptID1      OptID2      OptID3      OptID4
----------- ----------- ----------- ----------- ----------- -----------
11          56          116         117         NULL        NULL
11          57          118         119         NULL        NULL
11          60          124         125         126         NULL
11          62          129         130         131         NULL

Upvotes: 1

Related Questions