Post reply

Adding columns to a table

  • June 27, 2006 at 9:20 am

    #171494

    Hi Gurus,

    I'm reading two columns of data C1 and C2 from Table A by Time. C1 is my key field (material) and C2 is a qty field. See below

    Time      C1         C2

    Period1  Part 1     2

    Period2  Part 1     4

    The resultant table I require is:

    C1       C2_P1     C2_P2

    Part1      2            4

    I can extract the records I need by the time period no problem, but I want to write these records as per the layout above (by Period) by identifying the correct columns (using alias??)

    Thanks in advance.

    Mike

     

  • June 27, 2006 at 10:00 am

    #646113

    Something like this?

    DECLARE @test-2 TABLE (TimeSlot VARCHAR(10), C1 VARCHAR(10), C2 TINYINT)

    INSERT @Test

    SELECT 'Period1', 'Part 1', 2 UNION ALL

    SELECT 'Period2', 'Part 1', 4

    SELECT * FROM @test-2

    SELECT  C1,

      MAX(CASE WHEN TimeSlot = 'Period1' THEN C2 ELSE '' END) C2_P1,

       MAX(CASE WHEN TimeSlot = 'Period2' THEN C2 ELSE '' END) C2_P2

    FROM  @Test

    GROUP BY C1

    N 56°04'39.16"
    E 12°55'05.25"

  • June 27, 2006 at 10:02 am

    #646118

    Thank you Peter. I shall try this tomorrow.

    Mike

  • June 28, 2006 at 4:37 am

    #646326

    Thanks Peter. That worked great!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply