Compare Rows with different Values in tsql

Question

I am using a Microsoft Server-2008 and I have a table like this:

Name | Houre | Value
Hst1 | 4 | 45
Hst1 | 5 | 12
Hst1 | 6 | 88
Hst2 | 4 | 82
Hst2 | 5 | 7
Hst2 | 6 | 12

And I want to get something like this:

Name | 4  | 5  | 6 
Hst1 | 45 | 12 | 88
Hst2 | 82 | 7  | 12

Can you help me?

It's called a pivot table. If you have a indeterministic number of columns, then it's a dynamic pivot several SQL server examples exist on stack already. — xQbert, Nov 29 '16 at 15:30
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — xQbert, Nov 29 '16 at 15:30

score 0 · Answer 1 · answered Nov 29 '16 at 15:31

Depending if you only have 4,5,6 you can do a conditional agregation, other wise you need a dynamic PIVOT

SELECT Name, 
       MAX(CASE WHEN Houre = 4 THEN Value ELSE 0 END) as [4],
       MAX(CASE WHEN Houre = 5 THEN Value ELSE 0 END) as [5],
       MAX(CASE WHEN Houre = 6 THEN Value ELSE 0 END) as [6]
FROM YourTable
GROUP BY Name

score 0 · Answer 2 · answered Nov 29 '16 at 15:33

Use PIVOT!

SELECT *
FROM (
  VALUES ('Hst1', 4, 45),
         ('Hst1', 5, 12),
         ('Hst1', 6, 88),
         ('Hst2', 4, 82),
         ('Hst2', 5, 7),
         ('Hst2', 6, 12)
) t(name, hour, value)
PIVOT (
  max(value) FOR hour IN ([4], [5], [6])
) t

Compare Rows with different Values in tsql

2 Answers2