How to display sql row data as a column wise

Question

I've a table for sample name that contains the following columns firmnesssID, SampleTestingID and Firmness:

table

Now, I need to display output like the image below. I’m assuming there are maximum 40 records for a particular SampleTestingID and I want to show Firmness values under column header like 1a 1b 2a 2b 3a 3b ... 20a 20b (40 columns) for every distinct SampleTestingID.

I also tried to achieve the same by PIVOT the data but I was unable to get desired output. Here is what I was doing:

SELECT P.SampleTestingId, IsNull(P.[1], 0) as Wk1, IsNull(P.[2], 0) as Wk2
    , IsNull(P.[3], 0) as Wk3, IsNull(P.[4], 0) as Wk4
    , IsNull(P.[5], 0) as Wk5, IsNull(P.[6], 0) as Wk6
    , IsNull(P.[7], 0) as Wk7, IsNull(P.[8], 0) as Wk8
    , IsNull(P.[9], 0) as Wk9
FROM
(
    SELECT SampleTestingId, Firmness,[FirmnessID]
    FROM dbo.ftatestdata  WHERE SampletestingId=243 AND Isdeleted=0
) I
PIVOT
(
    SUM([FirmnessID])
    FOR [Firmness] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])
) as P

and

DECLARE @cols NVARCHAR (MAX)
  SELECT @cols = COALESCE (@cols + ', [' + Convert(varchar, FirmnessID) + ']', '[' + Convert(varchar, FirmnessID) + ']') 
  FROM  FtaTestData  
  WHERE SampleTestingID=243 AND IsDeleted=0 order by SampleTestingID
  print @cols

DECLARE @Query VARCHAR(MAX)

SET @Query= 'SELECT '+ @cols +' FROM (SELECT FirmnessID, Firmness
    FROM FtaTestData WHERE SampleTestingID=243 AND IsDeleted=0) P PIVOT ( MAX(Firmness) FOR FirmnessID IN ( '+@cols+' ) ) AS pvt'

print @Query

EXEC(@Query)

enter image description here

Search for `pivot query` there are hundreds .... even thousands examples everywhere :) — krokodilko, Dec 21 '13 at 19:28
possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — krokodilko, Dec 21 '13 at 19:29

score 0 · Answer 1 · answered Dec 22 '13 at 14:36

Somehow I manage to achieve the same using this...

 SELECT SampleTestingId, IsNull([1], 0) as '1a', IsNull([2], 0) as '1b'
        , IsNull([3], 0) as '2a', IsNull([4], 0) as '2b'
        , IsNull([5], 0) as '3a', IsNull([6], 0) as '3b'
        , IsNull([7], 0) as '4a', IsNull([8], 0) as '4b'
        , IsNull([9], 0) as '5a' ,IsNull([10], 0) as '5b'
        , IsNull([11], 0) as '6a', IsNull([12], 0) as '6b'
        , IsNull([13], 0) as '7a' ,IsNull([14], 0) as '7b'
        , IsNull([15], 0) as '8a', IsNull([16], 0) as '8b'
        , IsNull([17], 0) as '9a' ,IsNull([18], 0) as '9b'
        , IsNull([19], 0) as '10a', IsNull([20], 0) as '10b'
        , IsNull([21], 0) as '11a', IsNull([22], 0) as '11b'
        , IsNull([23], 0) as '12a', IsNull([24], 0) as '12b'
        , IsNull([25], 0) as '13a', IsNull([26], 0) as '13b'
        , IsNull([27], 0) as '14a', IsNull([28], 0) as '14b'
        , IsNull([29], 0) as '15a' ,IsNull([30], 0) as '15b'
        , IsNull([31], 0) as '16a', IsNull([32], 0) as '16b'
        , IsNull([33], 0) as '17a' ,IsNull([34], 0) as '17b'
        , IsNull([35], 0) as '18a', IsNull([36], 0) as '18b'
        , IsNull([37], 0) as '19a' ,IsNull([38], 0) as '19b'
        , IsNull([39], 0) as '20a', IsNull([40], 0) as '20b'  

        FROM(
        SELECT SampleTestingID,r_id,Firmness FROM (
            SELECT * ,ROW_NUMBER() OVER (PARTITION BY SampleTestingID ORDER BY SampleTestingID) as r_id
            FROM FTATestData) AS a WHERE r_id<=40
        )AS P
    PIVOT(
        MAX(Firmness) FOR r_id in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40])
    )AS Pvt

How to display sql row data as a column wise

1 Answers1