Row value as a column

Question

I have following records

DocNo   PrnOrd  DESCR   ITEMCD  AMT
1          1    D1      16844   43330
1          2    D2      16844   
1          7    D7      16844   2166.5
1         21    D21     16844   0.5
1          1    D1        937   58188
1          7    D7        937   2909.4
1         21    D21       937   
1         23    D23       937   -0.02

want to present like this

DocNo,ITEMCD,   D1,    D2,     D7, D21,  D23,
1    , 16844,43330,      , 2166.5, 0.5,     ,
1    ,   937,58188,2909.4,       ,    ,-0.02,

DESCR field value as a column and its AMT as a value

I believe a similar question is answered here: https://stackoverflow.com/questions/12808189/setting-column-values-as-column-names-in-the-sql-query-result — jared, Jun 07 '17 at 13:17

score 0 · Answer 1 · answered Jun 07 '17 at 13:13

Try as follows:

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

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

    set @query = 'SELECT DocNo   ,ITEMCD, ' + @cols + ' from 
                (
                    select DocNo ,ITEMCD  
                        , AMT
                        , DESCR
                    from temp
               ) x
                pivot 
                (
                     max(AMT)
                    for DESCR in (' + @cols + ')
                ) p '

exec (@query)

output:

*-*-------*-------*-------*----*----------*----------*     
|1| 937   |58188    |NULL |    |-0.02     |2909.4    |     
*-*-------*-------*-------*----*----------*----------*     
|1| 16844   |43330  |     |0.5 |    NULL  |2166.5    |      
*-*-------*-------*-------*----*----------*----------*

score 0 · Answer 2 · answered Jun 07 '17 at 13:15

0

Pivot is your best option here. Hard to answer fully without a little more detail; table names etc.

answered Jun 07 '17 at 13:15

Kit

29
2

Row value as a column

2 Answers2