CODEWITHSUNDEEP
sqlsql-server
fore_right
fore_right

Reputation: 69

sql server dataset rows to columns

my datatable looks like this in SQL Server:

grp   name    attribute    value    result
===========================================
a     jon        ht         66       pass
a     jon        wt         150      pass
a     jon        age        36       fail
a     bob        wt         168      fail
a     bob        age        50       pass

and i want to get the result to look like:

grp   name    ht     wt    age   ht_result   wt_result    age_result
====================================================================
a      jon    66     150   36      pass         pass        fail
a      bob    null   168   50      null         fail        pass

I've tried using a Pivot table, but i cant figure out how to generate the ht_result, wt_result, and age_result columns.

Thanks.

Upvotes: 3

Views: 67

Answers (2)

Habeeb
Habeeb

Reputation: 1040

SELECT PivotTable.grp,PivotTable.name,PivotTable.[ht], PivotTable.[wt], PivotTable.[age],res.[ht] ht_res, res.[wt] wt_res, res.[age] age_res
FROM
(SELECT grp, name,attribute,value 
    FROM tableName) AS SourceTable
PIVOT
(
max(value) FOR attribute IN ([ht],[wt],[age])

) AS PivotTable
Join 
(SELECT grp,name,PivotTable.[ht], PivotTable.[wt], PivotTable.[age]
FROM
(SELECT grp, name,attribute,result 
    FROM tableName) AS SourceTable
PIVOT
(
max(result) FOR attribute IN ([ht],[wt],[age])

) AS PivotTable) res on res.grp=PivotTable.grp and res.name=PivotTable.name

Upvotes: 2

Raging Bull
Raging Bull

Reputation: 18747

Try this:

SELECT grp,name,MAX(ht) as ht,max(wt) as wt,max(age) as age,max(ht_result) as ht_result,max(wt_result) as wt_result,max(age_result) as  age_result 
FROM 
(SELECT grp,name,CASE attribute WHEN 'ht' THEN value END as ht,
    CASE attribute WHEN 'wt' THEN value END as wt,
    CASE attribute WHEN 'age' THEN value END as age,
    CASE attribute WHEN 'ht' THEN result END as ht_result,
    CASE attribute WHEN 'wt' THEN result END as wt_result,
    CASE attribute WHEN 'age' THEN result END as age_result
FROM TableName) T
GROUP BY grp,name
ORDER BY name desc

Result:

GRP   NAME  HT      WT      AGE     HT_RESULT       WT_RESULT     AGE_RESULT
a     jon   66      150     36      pass            pass          fail
a     bob   (null)  168     50      (null)          fail           pass

See result in SQL Fiddle.

Upvotes: 3

Related Questions