1

I have a table tblItems with records having the same UniqueID and Description but with different dates and amount.

Now I want to show the records by grouping them together using the UniqueIDs and then display the Amount under a dynamically created Date column using the same record.

Date range will be provided as parameter like start date and end date and based on the date range there might be N number of columns.

Described with the example....

tblItems table

╔════════════╦════════════════════╦══════════╦═════════╗
║ UNIQUECODE ║    DESCRIPTION     ║   DATE   ║ AMOUNT  ║
╠════════════╬════════════════════╬══════════╬═════════╣
║ ABCD       ║ Item Decsription 1 ║ 1/3/2014 ║ 2068    ║
║ PQRS       ║ Item Decsription 2 ║ 1/5/2014 ║ 412.104 ║
║ PQRS       ║ Item Decsription 2 ║ 1/5/2014 ║ 635.448 ║
║ UVWX       ║ Item Decsription 3 ║ 1/5/2014 ║ 214.52  ║
║ UVWX       ║ Item Decsription 3 ║ 1/7/2014 ║ 827.2   ║
╚════════════╩════════════════════╩══════════╩═════════╝

I want to display this result as:

╔════════════╦════════════════════╦══════════╦══════════╦══════════╦══════════╗
║ UNIQUECODE ║    DESCRIPTION     ║ 1/3/2014 ║ 1/5/2014 ║ 1/7/2014 ║  Total   ║
╠════════════╬════════════════════╬══════════╬══════════╬══════════╬══════════╣
║ ABCD       ║ Item Decsription 1 ║     2068 ║ 0        ║ 0        ║ 2068     ║
║ PQRS       ║ Item Decsription 2 ║        0 ║ 1047.552 ║ 0        ║ 1047.552 ║
║ UVWX       ║ Item Decsription 3 ║        0 ║ 214.52   ║ 827.2    ║ 1041.72  ║
╚════════════╩════════════════════╩══════════╩══════════╩══════════╩══════════╝
1
1

Your best bet is probably to use dynamic SQL to pivot your data. Here try this out.

IF OBJECT_ID('tempdb..#tblItems') IS NOT NULL
    DROP TABLE #tblItems;
IF OBJECT_ID('tempdb..##tblItemsTotal') IS NOT NULL
    DROP TABLE ##tblItemsTotal;

CREATE TABLE #tblItems
(
    UniqueCode CHAR(4),
    [Description] VARCHAR(100),
    [Date] Date,
    Amount Decimal(18,3)
);

INSERT INTO #tblItems
VALUES  ('ABCD','Item Description 1','01/03/2014',2068),
        ('PQRS','Item Description 2','01/05/2014',412.104),
        ('PQRS','Item Description 2','01/07/2014',635.448),
        ('UVWX','Item Description 3','01/05/2014',214.52),
        ('UVWX','Item Description 3','01/07/2014',827.2);

--Now that I've set up the problem, here's for the actual code

--Declare your date range and variables to hold column lists
DECLARE @startDate DATE = '01/03/2014',
        @endDate DATE = '01/07/2014',
        @SelectCols VARCHAR(MAX),
        @PivotCols VARCHAR(MAX);

--Caluclate column lists
SELECT  @SelectCols =  COALESCE(@SelectCols + ',','') + 'ISNULL(pvt.' + QUOTENAME(CAST([Date] AS VARCHAR(25))) + ',0) AS ' + QUOTENAME(CAST([Date] AS VARCHAR(25))),
        @PivotCols =  COALESCE(@PivotCols + ',','') + QUOTENAME(CAST([Date] AS VARCHAR(10)))
FROM    #tblItems
WHERE   [Date] BETWEEN @startDate AND @endDate
GROUP BY [Date];

--Calculate your total within data range
SELECT  UniqueCode,
        [Description],
        SUM(amount) Total INTO ##tblItemsTotal
FROM #tblItems
WHERE [Date] BETWEEN @startDate AND @endDate
GROUP BY UniqueCode,[Description];

--Use dynamic SQL to pivot your data and join to ##tblItemsTotal for the Total column
EXEC
(
    'SELECT pvt.UniqueCode,
            pvt.[Description],' +
            @selectCols +
            ',B.Total
     FROM #tblItems A
     PIVOT
     (
        SUM(Amount) FOR [Date] IN (' + @PivotCols + ')
     ) pvt
     INNER JOIN ##tblItemsTotal B 
     ON pvt.UniqueCode = B.UniqueCode'
)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.