Post reply

Pivot Table?

  • January 8, 2003 at 6:45 pm

    #81380

    I have table A

    Year Q1 Q2 Q3 Q4

    1990 15 18 58 25

    1991 25 45 52 54

    1992 32 54 52 87

    and table B

    Year QTR AMT

    1990 Q1

    1990 Q2

    1990 Q3

    1990 Q4

    1991 Q1

    1991 Q2

    1991 Q3

    1991 Q4

    1992 Q1

    1992 Q2

    1992 Q3

    1992 Q4

    How do I populate the value of AMT from table A into table B?

    The table design is weird but don't ask me because I didn't make it. Thanks.

  • January 9, 2003 at 1:28 am

    #445918

    If you know the columns in table A, it's quite easy:

    DECLARE @fld VARCHAR(4)

    DECLARE @T INT

    DECLARE @sql VARCHAR(255)

    SELECT @T = 0

    WHILE @T<4

    BEGIN

    SET @T = @T + 1

    SET @fld = 'Q' + CONVERT(CHAR(1),@T)

    SET @sql = 'UPDATE B SET B.AMT = A.' + @fld + ' FROM A INNER JOIN B ON A.YEAR = B.YEAR WHERE B.QTR=''' + @fld + ''''

    EXEC(@SQL)

    END

  • January 9, 2003 at 3:33 am

    #445919

    If you know the column names in your table, you could use a UNION statement in lieu of dynamic SQL.

    declare @tableA table (yr int, q1 int, q2 int, q3 int, q4 int)

    declare @tableB table (Year int, QTR varchar(2), AMT int)

    insert into @tableA values (1990, 15, 18, 58, 25)

    insert into @tableA values (1991, 25, 45, 52, 54)

    insert into @tableA values (1992, 32, 54, 52, 87)

    select * from @tableA

    insert into @tableB

    select yr as Year, 'Q1' as QTR, q1 as AMT from @tableA

    union

    select yr as Year, 'Q2' as QTR, q2 as AMT from @tableA

    union

    select yr as Year, 'Q3' as QTR, q3 as AMT from @tableA

    union

    select yr as Year, 'Q4' as QTR, q4 as AMT from @tableA

    order by Year, QTR

    select * from @tableB

  • January 9, 2003 at 4:27 am

    #445921

    If both tables are already populated with the data then

    update b

    set b.AMT = (CASE

    WHEN b.QTR = 'Q1' THEN a.Q1

    WHEN b.QTR = 'Q2' THEN a.Q2

    WHEN b.QTR = 'Q3' THEN a.Q3

    WHEN b.QTR = 'Q4' THEN a.Q4

    ELSE 0

    END)

    from b

    inner join [A] a on a.Year = b.Year

    Edited by - davidburrows on 01/09/2003 04:28:32 AM

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 9, 2003 at 4:54 am

    #445924

    If you know the column names in your table, you could use a UNION statement in lieu of dynamic SQL.

    declare @tableA table (yr int, q1 int, q2 int, q3 int, q4 int)

    declare @tableB table (Year int, QTR varchar(2), AMT int)

    insert into @tableA values (1990, 15, 18, 58, 25)

    insert into @tableA values (1991, 25, 45, 52, 54)

    insert into @tableA values (1992, 32, 54, 52, 87)

    select * from @tableA

    insert into @tableB

    select yr as Year, 'Q1' as QTR, q1 as AMT from @tableA

    union

    select yr as Year, 'Q2' as QTR, q2 as AMT from @tableA

    union

    select yr as Year, 'Q3' as QTR, q3 as AMT from @tableA

    union

    select yr as Year, 'Q4' as QTR, q4 as AMT from @tableA

    order by Year, QTR

    select * from @tableB

  • January 9, 2003 at 5:21 am

    #445927

    I prefer the method done by DavidBurrows.

  • January 9, 2003 at 11:52 am

    #445972

    Excellent post, David. I was working on a complicated cursor since my real table A has more than 100 columns with different datatypes.

    Thanks.

  • January 9, 2003 at 4:51 pm

    #446016

    Hi SQL Gurus,

    I made up a sample table similar to my real table. So all sripts here work as desired. However in my real table it is complicated due to the datatype. Below is what would be a scaled down version of my table. I got "Syntax error converting the varchar value 'aa' to a column of data type int."

    I tried CAST but it won't work. I tried to changed the datatype in table Detail to varchar for all columns, same error. Most other scripts got same problem. Please help to populate the data into column Val. My head is solid now, can't think of anything else.

    CREATE TABLE Detail

    (D_ID INT,

    Col1 VARCHAR (10),

    Col2 VARCHAR (10),

    Col3 FLOAT,

    Col4 NUMERIC(5,2),

    Col5 INT)

    GO

    SELECT * FROM Detail

    INSERT Detail VALUES (1000,'aa','AAA',100,145.02,5)

    INSERT Detail VALUES (1001,'bb','BBB',150,0,10)

    INSERT Detail VALUES (1002,'cc','CCC',150.25,0.75,15)

    GO

    CREATE TABLE Detail_Rev1

    (D_ID int,

    Col varchar (10),

    Val varchar (100))

    SELECT * FROM Detail_Rev1

    insert Detail_Rev1 (D_ID,Col) values (1000,'Col1')

    insert Detail_Rev1 (D_ID,Col) values (1000,'Col2')

    insert Detail_Rev1 (D_ID,Col) values (1000,'Col3')

    insert Detail_Rev1 (D_ID,Col) values (1000,'Col4')

    insert Detail_Rev1 (D_ID,Col) values (1000,'Col5')

    insert Detail_Rev1 (D_ID,Col) values (1002,'Col1')

    insert Detail_Rev1 (D_ID,Col) values (1002,'Col2')

    insert Detail_Rev1 (D_ID,Col) values (1002,'Col3')

    insert Detail_Rev1 (D_ID,Col) values (1002,'Col4')

    insert Detail_Rev1 (D_ID,Col) values (1002,'Col5')

    insert Detail_Rev1 (D_ID,Col) values (1003,'Col1')

    insert Detail_Rev1 (D_ID,Col) values (1003,'Col2')

    insert Detail_Rev1 (D_ID,Col) values (1003,'Col3')

    insert Detail_Rev1 (D_ID,Col) values (1003 ,'Col4')

    insert Detail_Rev1 (D_ID,Col) values (1003,'Col5')

    SELECT * FROM Detail

    SELECT * FROM Detail_Rev1

    update b

    set b.Val = (CASE

    WHEN b.Col = 'Col1' THEN a.Col1

    WHEN b.Col = 'Col2' THEN a.Col2

    WHEN b.Col = 'Col3' THEN a.Col3

    WHEN b.Col = 'Col4' THEN a.Col4

    WHEN b.Col = 'Col5' THEN a.Col5

    ELSE 0

    END)

    from [Detail_Rev1] b

    inner join [Detail] a on a.D_ID = b.D_ID

  • January 9, 2003 at 5:51 pm

    #446020

    OK I got it. The problem is with ELSE 0 in the update statement.

    http://dbforums.com/archives/t313796.html gave me the hint.

    Thanks everyone.

Viewing 9 posts - 1 through 8 (of 8 total)

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