CODEWITHSUNDEEP
sqlsql-server
Jean-Francois Lavigne
Jean-Francois Lavigne

Reputation: 67

Transposing a column values into column headers

I have a table like this:

+-------------+-----------+------------+
| sample_name | test_name | test_value |
+-------------+-----------+------------+
| s1          | t1        | 1.5        |
| s2          | t2        | 3          |
| s3          | t1        | 8          |
| s4          | t3        | 5          |
+-------------+-----------+------------+

And I want to put the test_names as column headers like this

+-------------+------+------+------+
| sample_name |  t1  |  t2  |  t3  |
+-------------+------+------+------+
| s1          | 1.5  | null | null |
| s2          | null | 3    | null |
| s3          | 8    | null | null |
| s4          | null | null | 5    |
+-------------+------+------+------+

I have come up with a convoluted solution using a temporary table, dynamic sql and while loops but its slow and would like to know, is there a way to do it with only a select?

thanks

Upvotes: 1

Views: 173

Answers (3)

void
void

Reputation: 7890

if there are too much values for test_name then you may use pivot within dynamic sql:

declare @Names varchar(max) 
select @Names = coalesce(@Names + ', ', '') + test_name 
from (select distinct test_name from tbl) t

exec('
SELECT *
FROM tbl t
 PIVOT (SUM(test_value)
        FOR Test_name 
        IN ('+@Names+'))p')

SQLFIDDLE DEMO

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

DECLARE @Table TABLE(sample_name VARCHAR(10), test_name VARCHAR(10), test_value DECIMAL(10,2))
INSERT INTO @Table VALUES 
 ('s1','t1',1.5),        
 ('s2','t2',3  ),        
 ('s3','t1',8   ),      
 ('s4','t3',5  )


SELECT *
FROM @Table t
 PIVOT (SUM(test_value)
        FOR Test_name 
        IN (t1,t2,t3))p

Results:

| sample_name |     t1 |     t2 |     t3 |
|-------------|--------|--------|--------|
|          s1 |    1.5 | (null) | (null) |
|          s2 | (null) |      3 | (null) |
|          s3 |      8 | (null) | (null) |
|          s4 | (null) | (null) |      5 |

Upvotes: 1

Mr. Llama
Mr. Llama

Reputation: 20899

You're looking for PIVOTs which do that exact thing. They're most useful if there's a finite number of test_name values, but they can be used even if those values aren't known in advance.

Upvotes: 0

Related Questions