CODEWITHSUNDEEP
sql-server
MukkuP
MukkuP

Reputation: 103

convert rows to column using pivot SQL query

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

Upvotes: 0

Views: 45

Answers (1)

Jonny
Jonny

Reputation: 1161

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

Upvotes: 2

Related Questions