how to merge multiple rows one column data into a single row with multiple columns

Question

 

Please can anyone help me for the following?

 

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

 

for eg:

data

Date           Shift  Reading

01-MAR-08     1     879.880

01-MAR-08     2     854.858

01-MAR-08     3     833.836

02-MAR-08     1     809.810

02-MAR-08     2     785.784

02-MAR-08     3     761.760

 

i want output for the above as:

Date              Shift1         Shift2         Shift3

01-MAR-08     879.880       854.858       833.836

02-MAR-08     809.810       785.784       761.760

 

Please help me.

Answer 1

DECLARE @t TABLE(
Date datetime,
Shift int,
Reading float
)

INSERT INTO @t VALUES('03/01/2008', 1, 879.880);
INSERT INTO @t VALUES('03/01/2008', 2, 854.858);
INSERT INTO @t VALUES('03/01/2008', 3, 833.836);
INSERT INTO @t VALUES('03/02/2008', 1, 809.810);
INSERT INTO @t VALUES('03/02/2008', 2, 785.584);
INSERT INTO @t VALUES('03/02/2008', 3, 761.760);
-- SELECT * FROM @t;

SELECT Date, MAX(CASE WHEN Shift=1 THEN Reading END) AS Shift1,
MAX(CASE WHEN Shift=2 THEN Reading END) AS Shift2,
MAX(CASE WHEN Shift=3 THEN Reading END) AS Shift3
FROM @t
GROUP By Date;

Date                    Shift1                 Shift2                 Shift3
----------------------- ---------------------- ---------------------- ----------------------
2008-03-01 00:00:00.000 879.88                 854.858                833.836
2008-03-02 00:00:00.000 809.81                 785.584                761.76


Thanks,
Zuomin

Answer 2

What SQL Server are you using? 2K or 2K5?
The SQL script provided by zuomin will work for both 2000 and 2005

However, in 2K5, there is a new feature called the PIVOT command.

Please review the articles below:
- http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx
- http://www.devx.com/dbzone/Article/28165

Answer 3

Thanks a lot Zuomin

Answer 4

i m using sql server 2005, i'll be appreciated if you provide the same output by using pivot command.

 

thnx

Answer 5

Here you go..

 

change the select statement in Zuomin's code... as i used his code to create table variabl..

 

Code Snippet

Select date, [1] Shift1, [2] shift2, [3] shift3

from @T t

Pivot (

max(Reading)

for shift in ([1],[2],[3])) as pvt

order by date