how to spread a column using sql query?

Question

I have the following table

SourceTbl

FileNum    EventCode  ChargeDescrip       
510-1        DP           steal          
510-1        DP          possession          
510-2        DP          robbery          
510-2        DP           firearm          
510-2        DP           delivery          
510-3        DP          robbery

I want to spread this data based on chargedescrip column

DestTbl

FileNum    EventCode  Charge1,     Charge2      Charge3 
510-1        DP           steal    possession     
510-2        DP          robbery   firearm      delivery
510-3        DP          robbery

How do I do this using a SQL-Query? I found that I might be able to use the PIVOT clause. But I do go about using it.

See if this helps: https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql — Edney Holder, May 15 '19 at 21:01

score 0 · Answer 1 · answered May 15 '19 at 22:07

You have to use ROW_NUMBER and use that to generate your Charge1, Charge2, Charge3....columns.

WITH Cases
AS (
    SELECT [FileNum]
    ,[EventCode]
    ,[ChargeDescrip]
    ,ROW_NUMBER() OVER (
        PARTITION BY [FileNum] ORDER BY [FileNum] ASC
        ) 'RN'
FROM SourceTbl
)
SELECT [FileNum]
,[EventCode]
,[1] Charge1
,[2] Charge2
,[3] Charge3
FROM Cases
pivot(max([ChargeDescrip]) FOR [RN] IN (
        [1]
        ,[2]
        ,[3]
        )) p

how to spread a column using sql query?

1 Answers1