Query help

  • Here is the DDL of the table:

    I need single row per invoicenumber and line and rest of the amount and comments should to go to additional columns  in the same row as shown (in the spreadsheet).

    What would be the best way to accomplish this?

    DROP TABLE IF EXISTS #temp1

    CREATE TABLE #temp1 (Program VARCHAR(10), Invnumbe INT, LinNumber INT, Amount DECIMAL(12,0), comments VARCHAR(100), DataSource VARCHAR(10))

    INSERT INTO #temp1 VALUES(552,1001,1,20.00, 'Program1', 'SMS')

    INSERT INTO #temp1 VALUES(662,1001,1,30.00, 'Program2', 'SMS1')

    INSERT INTO #temp1 VALUES(663,1001,2,40.00, 'Program1', 'SMS2')

    INSERT INTO #temp1 VALUES(664,1001,2,45.00, 'Program2', 'SMS3')

    INSERT INTO #temp1 VALUES(665,1001,2,60.00, 'Program3', 'SMS4')

    INSERT INTO #temp1 VALUES(666,1001,2,70.00, 'Program4', 'SMS3')

    INSERT INTO #temp1 VALUES(667,1001,2,80.00, 'Program5', 'SMS4')

     

     

     

     

     

    Attachments:
    You must be logged in to view attached files.
  • This can be achieved using a Cross-Tab query.

    Static and Dynamic cross-tab queries are dealt with expertly and in great detail in these two Jeff Moden articles:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    SELECT InvoiceNumber = src.Invnumbe
    , LineNumber = src.LinNumber
    , Program1 = MAX(CASE WHEN src.comments = 'Program1' THEN src.comments END)
    , Amount1 = MAX(CASE WHEN src.comments = 'Program1' THEN src.Amount END)
    , Program2 = MAX(CASE WHEN src.comments = 'Program2' THEN src.comments END)
    , Amount2 = MAX(CASE WHEN src.comments = 'Program2' THEN src.Amount END)
    , Program3 = MAX(CASE WHEN src.comments = 'Program3' THEN src.comments END)
    , Amount3 = MAX(CASE WHEN src.comments = 'Program3' THEN src.Amount END)
    , Program4 = MAX(CASE WHEN src.comments = 'Program4' THEN src.comments END)
    , Amount4 = MAX(CASE WHEN src.comments = 'Program4' THEN src.Amount END)
    , Program5 = MAX(CASE WHEN src.comments = 'Program5' THEN src.comments END)
    , Amount5 = MAX(CASE WHEN src.comments = 'Program5' THEN src.Amount END)
    FROM #temp1 as src
    GROUP BY src.Invnumbe, src.LinNumber;
  • I need single row per invoicenumber

    Yet you're showing two lines of output for the same invoice number??

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • SQL_Surfer wrote:

    I need single row per invoicenumber and line

  • Soultuion DesNorton gave worked like a champ. Thank you so much!!

Viewing 5 posts - 1 through 5 (of 5 total)

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