DynamicPIVOTQueries.How toPrint OrderID Also in Final OutPut

Question

The following is the TSQL for creating an Order Master Table and inserting some dummy data into the table:

CREATE TABLE [dbo].[OrderMaster](

[OrderId] [int] IDENTITY(1,1) NOT NULL,

[OrderDate] [date] NULL,

[OrderAmount] [money] NULL,

CONSTRAINT [PK_OrderMaster] PRIMARY KEY CLUSTERED

(

[OrderId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO OrderMaster VALUES('2014-05-01',3500),

('2014-05-02',3000),

('2014-05-03',2800),

('2014-05-04',4500),

('2014-05-05',350)

Required output



Solution

We can write a hard-coded PIVOT if we know all the possible values that need to pivoted on. Using the following procedure we can write a PIVOT query dynamically.

Step 1: Declaring required variables

DECLARE @columnscsv VARCHAR(MAX)

DECLARE @Sumcolumnscsv VARCHAR(MAX)

DECLARE @sql VARCHAR(MAX)

Step 2: PIVOT query required column that contains the values that will become the column header. The following query helps us to create a column name string dynamically.

SELECT @columnscsv = COALESCE(@columnscsv + '],[','') + CAST(orderDate as VARCHAR(10))

FROM OrderMaster

GROUP BY OrderDate

SET @columnscsv = '[' + @columnscsv + ']'

print @columnscsv

The following is the output of the preceding query:

Step 3: The following query helps us to create an output columns name string with summation:

SELECT @Sumcolumnscsv = COALESCE(@Sumcolumnscsv + 'sum(isnull([','') + CAST(orderDate as VARCHAR(10)) + '],0)) as [' + CAST(orderDate as VARCHAR(10)) + '],'

FROM OrderMaster

GROUP BY OrderDate

SET @Sumcolumnscsv = 'sum(isnull([' + LEFT(@Sumcolumnscsv, LEN(@Sumcolumnscsv) - 1)

print @Sumcolumnscsv

The following is the output of the preceding query:

Step 4: Writing Final query

SET @sql = 'SELECT ''Total Amount'' as Date, ' + @Sumcolumnscsv + ' FROM OrderMaster ' +

' PIVOT ' +

' ( ' +

' sum(OrderAmount)' +

' FOR OrderDate IN (' + @columnscsv + ') ) AS PivotTable'

EXEC (@sql)

Final Output