convert rows to column using pivot SQL query

Question

I have some data in following format :

https://i.sstatic.net/SpHnz.png

could some one plz tell me how to pivot it to:

https://i.sstatic.net/wulVc.png

I have multiple set of such rows for each BatchNo and TransactionDate and want one batchno to be pivoted to one row and so on....

Update from comment a tried SQL not working (cf. comments on details):

SELECT 
  [1] UnitId, [2] Time, [4] Date, [5] A, [6] a, [8] b, [9] c, [10] ABT, 
  [11] BatLevel, [12] SC, [13] GC, [14] OB, [17] CO, [18] VO, [19] PO, 
  [20] PU, [21] CCB 
 from ( 
    select * 
     from [ProductTCPTransaction] 
 ) src 
 PIVOT ( 
    COUNT(TransactionDate) 
    FOR ParameterID 
    in ([1],[2],[4],[5],[6],[8],[9],[10],[11],[12], 
        [13],[14],[17],[18],[19],[20],[21]) 
 ) piv

Hm, whatever tabular form means for a minimal sample ... but the SQL you tried (best effort) or where you got stuck, should be OK to type in, right? Someone offering a solution, is expected to type in data, to check before handing it out? Interesting idea ;-) — Dilettant
– Dilettant, Commented Jul 8, 2016 at 11:46
Oh sure the query I tried is : SELECT [1] UnitId, [2] Time, [4] Date, [5] A, [6] a, [8] b, [9] c, [10] ABT, [11] BatLevel, [12] SC, [13] GC, [14] OB, [17] CO, [18] VO, [19] PO, [20] PU, [21] CCB from ( select * from [ProductTCPTransaction] ) src PIVOT ( COUNT(TransactionDate) FOR ParameterID in ([1],[2],[4],[5],[6],[8],[9],[10],[11],[12], [13],[14],[17],[18],[19],[20],[21]) ) piv — MukkuP
– MukkuP, Commented Jul 8, 2016 at 11:49

Jonny · Accepted Answer · 2016-07-08 14:43:35Z

So how about this, you can add the rest yourself ;)

        IF OBJECT_ID('tempdb..#Unpiv') IS NOT NULL DROP TABLE #Unpiv

    CREATE TABLE #Unpiv
    (
        ParameterId int not null,
        ParameterValue varchar(30) not null,
        TransactionDate Date not null
    )

    INSERT INTO #Unpiv
    SELECT 1, '+917506711342', '07/06/16'
    UNION
    SELECT 2, '172500', '07/06/16'
    UNION
    SELECT 4, '310316', '07/06/16'
    UNION
    SELECT 5, 'A', '07/06/16'
    UNION
    SELECT 6, '0', '07/06/16'
    UNION
    SELECT 8, '1', '07/06/16'
    UNION
    SELECT 9, '1', '07/06/16'
    UNION
    SELECT 10, '038', '07/06/16'
    UNION
    SELECT 11, '108', '07/06/16'
    UNION
    SELECT 12, '010', '07/06/16'
    UNION
    SELECT 13, '010', '07/06/16'
    UNION
    SELECT 14, '005', '07/06/16'
    UNION
    SELECT 17, '000', '07/06/16'
    UNION
    SELECT 18, '238', '07/06/16'
    UNION
    SELECT 19, '000', '07/06/16'
    UNION
    SELECT 20, '00000', '07/06/16'
    UNION
    SELECT 21, '00034', '07/06/16'

    SELECT
        [1], [2], [4], [5], [6], [8], [9], [10], [11], [12], [13], TransactionDate
    FROM
        #Unpiv
    PIVOT (MAX(ParameterValue) FOR ParameterId IN ([1], [2], [4], [5], [6], [8], [9], [10], [11], [12], [13])) P

Collectives™ on Stack Overflow

convert rows to column using pivot SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related