none
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.
    Monday, March 03, 2008 8:06 AM

Answers

  • 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

    Monday, March 03, 2008 9:13 AM
  • 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
    Monday, March 03, 2008 9:26 AM
  • 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

     

     

     

     

    Wednesday, March 05, 2008 8:59 AM

All replies

  • 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

    Monday, March 03, 2008 9:13 AM
  • 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
    Monday, March 03, 2008 9:26 AM
  • Thanks a lot Zuomin
    Wednesday, March 05, 2008 8:38 AM
  • i m using sql server 2005, i'll be appreciated if you provide the same output by using pivot command.
     
    thnx
    Wednesday, March 05, 2008 8:40 AM
  • 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

     

     

     

     

    Wednesday, March 05, 2008 8:59 AM