Add Columns dynamically to SQL Query

Question

Post reply

Add Columns dynamically to SQL Query

c.vaibhav

SSChasing Mays

Points: 652
More actions
September 16, 2009 at 2:20 am

#135647

Hello Friends,
I am developing a query to generate Product-Sales Quantity Card for 1 customer.
The logic goes as follows:
User selects the customer name and the months and clicks on submit, now the query should extract the data for that customer in the following format:
P.Name | Month1 | Month2
ABC |||| 200 ||| 300
DEF |||| 400 ||| 500
GHI |||| 600 ||| 200
User may select any number of months.
The database has Date, CustomerName, ProductName & Quantity.
Can anybody please help.
Regards,
Vaibhav

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Looks like you need some PIVOT:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

If you post DDL and sample data fro your table I can help you writing the query.

Read this article on how posting sample data:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Regards

Gianluca

-- Gianluca Sartori

c.vaibhav SSChasing Mays Points: 652 More actions · Answer 2

Hi,

Thanks for your valuable time.

I have written the query to create the table:

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

ID Int Identity(1,1),

DateValue DATETIME,

CustomerName nvarchar(max),

Product nvarchar(max),

Quantity INT

)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(ID, DateValue, CustomerName, Product, Quantity)

SELECT '1','May 15 2009 12:00AM','Customer1','Product DAN','400' UNION ALL

SELECT '1','May 15 2009 12:00AM','Customer1','Product PEARL','1300' UNION ALL

SELECT '2','May 15 2009 12:00AM','Customer2','Product CAN','100' UNION ALL

SELECT '2','May 15 2009 12:00AM','Customer2','Product EDNI','100' UNION ALL

SELECT '2','May 15 2009 12:00AM','Customer2','Product DAN','200' UNION ALL

SELECT '6','May 15 2009 12:00AM','Customer3','Product PBI','50' UNION ALL

SELECT '7','May 15 2009 12:00AM','Customer4','Product C 100 W','500' UNION ALL

SELECT '8','May 15 2009 12:00AM','Customer5','Product P','500' UNION ALL

SELECT '9','May 15 2009 12:00AM','Customer1','Product SMJS','200' UNION ALL

SELECT '10','May 15 2009 12:00AM','Customer1','Product LF','500' UNION ALL

SELECT '11','May 19 2009 12:00AM','Customer2','Product HPN','250' UNION ALL

SELECT '12','May 15 2009 12:00AM','Customer2','Product PV','400' UNION ALL

SELECT '13','May 15 2009 12:00AM','Customer2','Product SBAG','500' UNION ALL

SELECT '14','May 15 2009 12:00AM','Customer3','Product P','400' UNION ALL

SELECT '15','May 19 2009 12:00AM','Customer4','Product SSG New','400' UNION ALL

SELECT '16','May 19 2009 12:00AM','Customer5','Product HPN','1000' UNION ALL

SELECT '17','May 19 2009 12:00AM','Customer1','Product DAI L','2000' UNION ALL

SELECT '18','May 19 2009 12:00AM','Customer1','Product A','1125' UNION ALL

SELECT '19','May 15 2009 12:00AM','Customer2','Product PEARL','500' UNION ALL

SELECT '20','May 15 2009 12:00AM','Customer2','Product P','1000' UNION ALL

SELECT '21','May 15 2009 12:00AM','Customer2','Product EWO','10' UNION ALL

SELECT '22','May 19 2009 12:00AM','Customer3','Product SWA','8' UNION ALL

SELECT '23','May 15 2009 12:00AM','Customer4','Product NF SPL','14' UNION ALL

SELECT '24','May 15 2009 12:00AM','Customer5','Product BI','4' UNION ALL

SELECT '25','May 15 2009 12:00AM','Customer1','Product LF','2' UNION ALL

SELECT '26','May 15 2009 12:00AM','Customer1','Product BI','5' UNION ALL

SELECT '27','May 15 2009 12:00AM','Customer2','Product U 2','1' UNION ALL

SELECT '28','May 19 2009 12:00AM','Customer2','Product CATSIL','2' UNION ALL

SELECT '29','May 16 2009 12:00AM','Customer2','Product P','2' UNION ALL

SELECT '30','May 16 2009 12:00AM','Customer3','Product CSLF','2' UNION ALL

SELECT '31','May 16 2009 12:00AM','Customer4','Product CWS','5' UNION ALL

SELECT '32','May 20 2009 12:00AM','Customer5','Product C 100 W','5' UNION ALL

SELECT '33','May 16 2009 12:00AM','Customer1','Primalose TM','2' UNION ALL

SELECT '34','May 20 2009 12:00AM','Customer1','Product DADLP','5' UNION ALL

SELECT '35','May 16 2009 12:00AM','Customer2','Treat 100','5' UNION ALL

SELECT '36','May 23 2009 12:00AM','Customer2','Product LDG','5' UNION ALL

SELECT '37','May 23 2009 12:00AM','Customer2','Product HPGSR Premium','5' UNION ALL

SELECT '38','May 23 2009 12:00AM','Customer3','Product B','3' UNION ALL

SELECT '39','May 23 2009 12:00AM','Customer4','Product DAN','5' UNION ALL

SELECT '40','May 23 2009 12:00AM','Customer5','Product C 100 W','5' UNION ALL

SELECT '41','May 23 2009 12:00AM','Customer1','Product HLA Premium','5' UNION ALL

SELECT '42','May 23 2009 12:00AM','Customer1','Product HLA Premium','23' UNION ALL

SELECT '43','May 23 2009 12:00AM','Customer2','Product DT','100' UNION ALL

SELECT '44','May 22 2009 12:00AM','Customer2','Product VL','50' UNION ALL

SELECT '45','May 22 2009 12:00AM','Customer2','Primalose TM','50' UNION ALL

SELECT '46','May 23 2009 12:00AM','Customer3','Product PBI','200' UNION ALL

SELECT '47','May 23 2009 12:00AM','Customer4','Product SHE','300' UNION ALL

SELECT '48','May 23 2009 12:00AM','Customer5','Product BI','400' UNION ALL

SELECT '49','May 23 2009 12:00AM','Customer1','Xol O','500' UNION ALL

SELECT '50','May 22 2009 12:00AM','Customer1','Xol UD','300' UNION ALL

SELECT '51','May 23 2009 12:00AM','Customer2','Softex AMP','250' UNION ALL

SELECT '52','May 23 2009 12:00AM','Customer2','Albex DBL','100' UNION ALL

SELECT '53','May 23 2009 12:00AM','Customer2','Product ASA','200' UNION ALL

SELECT '54','May 23 2009 12:00AM','Customer3','Product HPN','200' UNION ALL

SELECT '55','May 23 2009 12:00AM','Customer4','Productlon EPN','100' UNION ALL

SELECT '56','May 23 2009 12:00AM','Customer5','Productjet S','50' UNION ALL

SELECT '57','May 23 2009 12:00AM','Customer1','ProductAlbex BI','200' UNION ALL

SELECT '58','May 23 2009 12:00AM','Customer1','Productfix N','100' UNION ALL

SELECT '59','May 22 2009 12:00AM','Customer2','Productpro CDL','50' UNION ALL

SELECT '60','May 22 2009 12:00AM','Customer2','Productlon EPN','500' UNION ALL

SELECT '61','May 22 2009 12:00AM','Customer2','Productpro CDL','800' UNION ALL

SELECT '62','May 22 2009 12:00AM','Customer3','ProductAlbex BI','300' UNION ALL

SELECT '63','May 22 2009 12:00AM','Customer4','Productjet LF','400' UNION ALL

SELECT '64','May 23 2009 12:00AM','Customer5','Albex CMF','50' UNION ALL

SELECT '65','May 23 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '66','May 23 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '67','May 23 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '68','May 23 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '69','May 23 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '70','May 23 2009 12:00AM','Customer3','Product ECNF','100' UNION ALL

SELECT '71','May 22 2009 12:00AM','Customer4','Product ECNF','100' UNION ALL

SELECT '72','May 23 2009 12:00AM','Customer5','Product ECNF','100' UNION ALL

SELECT '73','May 22 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '74','May 23 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '75','May 23 2009 12:00AM','Customer2','Product NS','100' UNION ALL

SELECT '76','May 22 2009 12:00AM','Customer2','Product LDG','50' UNION ALL

SELECT '77','May 22 2009 12:00AM','Customer2','Treat 100','1400' UNION ALL

SELECT '78','May 23 2009 12:00AM','Customer3','Albecid HSB','50' UNION ALL

SELECT '79','May 22 2009 12:00AM','Customer4','Treat 100','100' UNION ALL

SELECT '80','May 23 2009 12:00AM','Customer5','Product UFO','50' UNION ALL

SELECT '81','May 22 2009 12:00AM','Customer1','Albeclean SR 50','50' UNION ALL

SELECT '82','May 23 2009 12:00AM','Customer1','Product ZAT','200' UNION ALL

SELECT '83','May 23 2009 12:00AM','Customer2','Nanosil Super','750' UNION ALL

SELECT '84','May 23 2009 12:00AM','Customer2','Albeclean SR 50','100' UNION ALL

SELECT '85','May 23 2009 12:00AM','Customer2','Ingo GOGO','10' UNION ALL

SELECT '86','May 23 2009 12:00AM','Customer3','Product AR ECO','100' UNION ALL

SELECT '87','May 22 2009 12:00AM','Customer4','Product A','150' UNION ALL

SELECT '88','May 22 2009 12:00AM','Customer5','Product A','150' UNION ALL

SELECT '89','May 22 2009 12:00AM','Customer1','Product A','150' UNION ALL

SELECT '90','May 22 2009 12:00AM','Customer1','Neutrachlor PDR','100' UNION ALL

SELECT '91','May 18 2009 12:00AM','Customer2','Product HDPE','100' UNION ALL

SELECT '92','May 18 2009 12:00AM','Customer2','Product BX','400' UNION ALL

SELECT '93','May 18 2009 12:00AM','Customer2','Product SHE','200' UNION ALL

SELECT '94','May 18 2009 12:00AM','Customer3','Neutrachlor PDR','50' UNION ALL

SELECT '95','May 18 2009 12:00AM','Customer4','Product AR ECO','500' UNION ALL

SELECT '96','May 17 2009 12:00AM','Customer5','Product ARM 100','500' UNION ALL

SELECT '97','May 17 2009 12:00AM','Customer1','Product C 100 W','500' UNION ALL

SELECT '98','May 18 2009 12:00AM','Customer1','Product A','1125' UNION ALL

SELECT '99','May 17 2009 12:00AM','Customer2','Product RD Pr','250' UNION ALL

SELECT '100','May 17 2009 12:00AM','Customer2','Productjet LF','200' UNION ALL

SELECT '101','May 17 2009 12:00AM','Customer2','ProductAlbex BI','300' UNION ALL

SELECT '102','May 17 2009 12:00AM','Customer3','Productfix N','100' UNION ALL

SELECT '103','May 17 2009 12:00AM','Customer4','Stabiliser EG','200' UNION ALL

SELECT '104','May 18 2009 12:00AM','Customer5','Productjet S','300' UNION ALL

SELECT '105','May 18 2009 12:00AM','Customer1','Product PN Spl','500' UNION ALL

SELECT '106','May 17 2009 12:00AM','Customer1','Product LDG','250' UNION ALL

SELECT '107','May 17 2009 12:00AM','Customer2','Product CMF','150' UNION ALL

SELECT '108','May 17 2009 12:00AM','Customer2','Product HPGSR Premium','50' UNION ALL

SELECT '109','May 18 2009 12:00AM','Customer2','Product PEARL 1','500' UNION ALL

SELECT '110','May 17 2009 12:00AM','Customer3','Lubricol ZB','1400' UNION ALL

SELECT '111','May 17 2009 12:00AM','Customer4','Atex LFJ','1000' UNION ALL

SELECT '112','May 17 2009 12:00AM','Customer5','Product HPN','400' UNION ALL

SELECT '113','May 17 2009 12:00AM','Customer1','Perox EPN','150' UNION ALL

SELECT '114','May 17 2009 12:00AM','Customer1','Product CA','500' UNION ALL

SELECT '115','May 16 2009 12:00AM','Customer2','Neutrachlor PDR','50' UNION ALL

SELECT '116','May 16 2009 12:00AM','Customer2','Product ECNF','200' UNION ALL

SELECT '117','May 16 2009 12:00AM','Customer2','Product HPN','500' UNION ALL

SELECT '118','May 17 2009 12:00AM','Customer3','Product SDN','500' UNION ALL

SELECT '119','May 17 2009 12:00AM','Customer4','Product ECNF','200' UNION ALL

SELECT '120','May 16 2009 12:00AM','Customer5','Product ECNF','200' UNION ALL

SELECT '121','May 17 2009 12:00AM','Customer1','Product ECNF','200' UNION ALL

SELECT '122','May 16 2009 12:00AM','Customer1','Product ECNF','200' UNION ALL

SELECT '123','May 16 2009 12:00AM','Customer2','Product NF 50','200' UNION ALL

SELECT '124','May 16 2009 12:00AM','Customer2','Product SDBL','300' UNION ALL

SELECT '125','May 16 2009 12:00AM','Customer2','Perox EPN','200' UNION ALL

SELECT '126','May 16 2009 12:00AM','Customer3','Safedye RFL','50' UNION ALL

SELECT '127','May 16 2009 12:00AM','Customer4','Product CMF N','200' UNION ALL

SELECT '128','May 16 2009 12:00AM','Customer5','Oxymagic VS Liquid','500' UNION ALL

SELECT '129','May 16 2009 12:00AM','Customer1','Safedye RFL','10' UNION ALL

SELECT '130','May 16 2009 12:00AM','Customer1','Safedye RFL','20' UNION ALL

SELECT '131','May 16 2009 12:00AM','Customer2','Albepet NIX','1000' UNION ALL

SELECT '132','May 16 2009 12:00AM','Customer2','Albewash DNS','1050' UNION ALL

SELECT '133','May 18 2009 12:00AM','Customer2','Product LDG','100' UNION ALL

SELECT '134','May 3 2009 12:00AM','Customer3','Treat 100','1550' UNION ALL

SELECT '135','May 3 2009 12:00AM','Customer4','Product AP','200' UNION ALL

SELECT '136','May 3 2009 12:00AM','Customer5','Product ARM 100','500' UNION ALL

SELECT '137','May 3 2009 12:00AM','Customer1','Product WAD Premium','500' UNION ALL

SELECT '138','May 18 2009 12:00AM','Customer1','Product SBAG','500' UNION ALL

SELECT '139','May 18 2009 12:00AM','Customer2','Product SMJS','300' UNION ALL

SELECT '140','May 18 2009 12:00AM','Customer2','Product A L','300' UNION ALL

SELECT '141','May 22 2009 12:00AM','Customer2','Product RRW','100' UNION ALL

SELECT '142','May 3 2009 12:00AM','Customer3','Product EWO S','300' UNION ALL

SELECT '143','May 3 2009 12:00AM','Customer4','Product N','500' UNION ALL

SELECT '144','May 3 2009 12:00AM','Customer5','Soprint PMP','500' UNION ALL

SELECT '145','May 5 2009 12:00AM','Customer1','Product D Paste','50' UNION ALL

SELECT '146','May 23 2009 12:00AM','Customer1','Product L 100','200' UNION ALL

SELECT '147','May 23 2009 12:00AM','Customer2','Product EG','200' UNION ALL

SELECT '148','May 23 2009 12:00AM','Customer2','Product ACL 100','20' UNION ALL

SELECT '149','May 23 2009 12:00AM','Customer2','Product SBAG','400' UNION ALL

SELECT '150','May 23 2009 12:00AM','Customer3','Product SWA','50' UNION ALL

SELECT '151','May 23 2009 12:00AM','Customer4','Product HPS Conc','100' UNION ALL

SELECT '152','May 23 2009 12:00AM','Customer5','Product NF','50' UNION ALL

SELECT '153','May 23 2009 12:00AM','Customer1','Product FSN','150' UNION ALL

SELECT '154','May 23 2009 12:00AM','Customer1','Product RLA','200' UNION ALL

SELECT '155','May 23 2009 12:00AM','Customer2','Product SDN','1000' UNION ALL

SELECT '156','May 23 2009 12:00AM','Customer2','Product FFC','500' UNION ALL

SELECT '157','May 5 2009 12:00AM','Customer2','Treat 100','200' UNION ALL

SELECT '158','May 24 2009 12:00AM','Customer3','Product LE','300' UNION ALL

SELECT '159','May 10 2009 12:00AM','Customer4','Biozyme A Plus','60' UNION ALL

SELECT '160','May 25 2009 12:00AM','Customer5','Product PCA','200' UNION ALL

SELECT '161','May 25 2009 12:00AM','Customer1','Product SP PR','300' UNION ALL

SELECT '162','May 25 2009 12:00AM','Customer1','Product NDL','150' UNION ALL

SELECT '163','May 25 2009 12:00AM','Customer2','Product ARM 100','1000' UNION ALL

SELECT '164','May 10 2009 12:00AM','Customer2','Product ASA','1000' UNION ALL

SELECT '165','May 10 2009 12:00AM','Customer2','Biocide 106','5' UNION ALL

SELECT '166','May 26 2009 12:00AM','Customer3','Product BX','400' UNION ALL

SELECT '167','May 26 2009 12:00AM','Customer4','Product ZAT','400' UNION ALL

SELECT '168','May 26 2009 12:00AM','Customer5','Nanosil Super','1600' UNION ALL

SELECT '169','May 11 2009 12:00AM','Customer1','Product CWS','50' UNION ALL

SELECT '170','May 26 2009 12:00AM','Customer1','Product ASA','2100' UNION ALL

SELECT '171','May 27 2009 12:00AM','Customer2','Product ASA','1050' UNION ALL

SELECT '172','May 27 2009 12:00AM','Customer2','Product C 100 W','1000' UNION ALL

SELECT '173','May 27 2009 12:00AM','Customer2','Product P 89','200' UNION ALL

SELECT '174','May 27 2009 12:00AM','Customer3','Product HTX 520','50' UNION ALL

SELECT '175','May 27 2009 12:00AM','Customer4','Product NF 68','1000' UNION ALL

SELECT '176','May 27 2009 12:00AM','Customer5','Product ECNF','100' UNION ALL

SELECT '177','May 11 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '178','May 11 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '179','May 11 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '180','May 11 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '181','May 27 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '182','May 27 2009 12:00AM','Customer3','Product ECNF','100' UNION ALL

SELECT '183','May 27 2009 12:00AM','Customer4','Product ECNF','100' UNION ALL

SELECT '184','May 27 2009 12:00AM','Customer5','Product ECNF','100' UNION ALL

SELECT '185','May 27 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '186','May 27 2009 12:00AM','Customer1','Product L4T M','500' UNION ALL

SELECT '187','May 11 2009 12:00AM','Customer2','Product WFF','1000' UNION ALL

SELECT '188','May 11 2009 12:00AM','Customer2','Product ARM 100','1000' UNION ALL

SELECT '189','May 11 2009 12:00AM','Customer2','Product ASA','2000' UNION ALL

SELECT '190','May 27 2009 12:00AM','Customer3','Product ASA L','1500' UNION ALL

SELECT '191','May 27 2009 12:00AM','Customer4','Product SBAG','50' UNION ALL

SELECT '192','May 27 2009 12:00AM','Customer5','Product DAI L','2000' UNION ALL

SELECT '193','May 27 2009 12:00AM','Customer1','Product BAP Pdr','5' UNION ALL

SELECT '194','May 27 2009 12:00AM','Customer1','Product AS 1','50' UNION ALL

SELECT '195','May 27 2009 12:00AM','Customer2','Hydrophil','50' UNION ALL

SELECT '196','May 17 2009 12:00AM','Customer2','Product CMF','200' UNION ALL

SELECT '197','May 17 2009 12:00AM','Customer2','Product CDL','200' UNION ALL

SELECT '198','May 17 2009 12:00AM','Customer3','Product HPGSR Premium','300' UNION ALL

SELECT '199','May 17 2009 12:00AM','Customer4','Albecid CAN','300' UNION ALL

SELECT '200','May 17 2009 12:00AM','Customer5','Product DAI N5','200' UNION ALL

SELECT '201','Jun 1 2009 12:00AM','Customer1','Product AR ECO','20' UNION ALL

SELECT '202','Jun 1 2009 12:00AM','Customer1','Product U Plus','20' UNION ALL

SELECT '203','Jun 1 2009 12:00AM','Customer2','Product U Plus','40' UNION ALL

SELECT '204','Jun 1 2009 12:00AM','Customer2','Product HPN','100' UNION ALL

SELECT '205','Jun 1 2009 12:00AM','Customer2','Aurozyme BP 36','3010' UNION ALL

SELECT '206','Jun 1 2009 12:00AM','Customer3','Product CMF N','1100' UNION ALL

SELECT '207','Jun 1 2009 12:00AM','Customer4','Product PN Spl','500' UNION ALL

SELECT '208','Jun 1 2009 12:00AM','Customer5','Product SP','50' UNION ALL

SELECT '209','Jun 1 2009 12:00AM','Customer1','Product LDG','50' UNION ALL

SELECT '210','Jun 1 2009 12:00AM','Customer1','Product LDG','200' UNION ALL

SELECT '211','Jun 1 2009 12:00AM','Customer2','Productlon EPN','500' UNION ALL

SELECT '212','Jun 1 2009 12:00AM','Customer2','Product DAN','400' UNION ALL

SELECT '213','Jun 1 2009 12:00AM','Customer2','Product HDPE','100' UNION ALL

SELECT '214','Jun 1 2009 12:00AM','Customer3','Treat 100','750' UNION ALL

SELECT '215','Jun 1 2009 12:00AM','Customer4','Product RWL','200' UNION ALL

SELECT '216','Jun 1 2009 12:00AM','Customer5','Nanosil Super','1000' UNION ALL

SELECT '217','Jun 1 2009 12:00AM','Customer1','Product NS 100','1000' UNION ALL

SELECT '218','Jun 1 2009 12:00AM','Customer1','Product ASA','50' UNION ALL

SELECT '219','Jun 1 2009 12:00AM','Customer2','Nanosil Super','2000' UNION ALL

SELECT '220','Jun 2 2009 12:00AM','Customer2','Treat 100','300' UNION ALL

SELECT '221','Jun 3 2009 12:00AM','Customer2','Product ASA','1000' UNION ALL

SELECT '222','Jun 3 2009 12:00AM','Customer3','Product NF 50','800' UNION ALL

SELECT '223','Jun 3 2009 12:00AM','Customer4','Product AD','50' UNION ALL

SELECT '224','Jun 3 2009 12:00AM','Customer5','Product LF','100' UNION ALL

SELECT '225','Jun 3 2009 12:00AM','Customer1','Primaproof VFS 50%','700' UNION ALL

SELECT '226','Jun 4 2009 12:00AM','Customer1','Product FFC','500' UNION ALL

SELECT '227','Jun 5 2009 12:00AM','Customer2','Product SMJS','300' UNION ALL

SELECT '228','Jun 5 2009 12:00AM','Customer2','Productjet LF','300' UNION ALL

SELECT '229','Jun 5 2009 12:00AM','Customer2','Product A','300' UNION ALL

SELECT '230','Jun 5 2009 12:00AM','Customer3','Oxymagic VS Liquid','300' UNION ALL

SELECT '231','Jun 5 2009 12:00AM','Customer4','Product BXN New','200' UNION ALL

SELECT '232','Jun 5 2009 12:00AM','Customer5','Product AFW','210' UNION ALL

SELECT '233','Jun 6 2009 12:00AM','Customer1','Treat 100','500' UNION ALL

SELECT '234','Jun 6 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '235','Jun 6 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '236','Jun 6 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '237','Jun 6 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '238','Jun 6 2009 12:00AM','Customer3','Product ECNF','100' UNION ALL

SELECT '239','Jun 6 2009 12:00AM','Customer4','Nanosil Super','1500' UNION ALL

SELECT '240','Jun 6 2009 12:00AM','Customer5','Treat 100','300' UNION ALL

SELECT '241','Jun 6 2009 12:00AM','Customer1','Product 200','200' UNION ALL

SELECT '242','Jun 6 2009 12:00AM','Customer1','Product SBAG','100' UNION ALL

SELECT '243','Jun 2 2009 12:00AM','Customer2','Product ASA','300' UNION ALL

SELECT '244','Jun 2 2009 12:00AM','Customer2','W 2000','100' UNION ALL

SELECT '245','Jun 2 2009 12:00AM','Customer2','Product O','1000' UNION ALL

SELECT '246','Jun 2 2009 12:00AM','Customer3','Product PCMD','300' UNION ALL

SELECT '247','Jun 2 2009 12:00AM','Customer4','Product LTD','200' UNION ALL

SELECT '248','Jun 2 2009 12:00AM','Customer5','Synthocat 100','50' UNION ALL

SELECT '249','Jun 2 2009 12:00AM','Customer1','Product NS','100' UNION ALL

SELECT '250','Jun 2 2009 12:00AM','Customer1','Product AFW','210' UNION ALL

SELECT '251','Jun 2 2009 12:00AM','Customer2','Treat 100','1150' UNION ALL

SELECT '252','Jun 2 2009 12:00AM','Customer2','Albecid CAN','200' UNION ALL

SELECT '253','Jun 2 2009 12:00AM','Customer2','Product ANZ','100' UNION ALL

SELECT '254','Jun 2 2009 12:00AM','Customer3','Product NDL','50' UNION ALL

SELECT '255','Jun 2 2009 12:00AM','Customer4','P 24','500' UNION ALL

SELECT '256','Jun 2 2009 12:00AM','Customer5','Product LFN','300' UNION ALL

SELECT '257','Jun 2 2009 12:00AM','Customer1','Product MA','200' UNION ALL

SELECT '258','Jun 2 2009 12:00AM','Customer1','Product PEARL','350' UNION ALL

SELECT '259','Jun 2 2009 12:00AM','Customer2','Product RLA','200' UNION ALL

SELECT '260','Jun 2 2009 12:00AM','Customer2','Product FFC','250' UNION ALL

SELECT '261','Jun 2 2009 12:00AM','Customer2','Product CDL','500' UNION ALL

SELECT '262','Jun 2 2009 12:00AM','Customer3','Product NIX H/C','100' UNION ALL

SELECT '263','Jun 2 2009 12:00AM','Customer4','Product HPS Premium','20' UNION ALL

SELECT '264','Jun 2 2009 12:00AM','Customer5','Product L4T','100' UNION ALL

SELECT '265','Jun 3 2009 12:00AM','Customer1','Desize WLD','20' UNION ALL

SELECT '266','Jun 3 2009 12:00AM','Customer1','Product C 100 W','500' UNION ALL

SELECT '267','Jun 3 2009 12:00AM','Customer2','ZL - 4 - ALM','1000' UNION ALL

SELECT '268','Jun 3 2009 12:00AM','Customer2','Atex LFJ','1000' UNION ALL

SELECT '269','Jun 3 2009 12:00AM','Customer2','Product SFC','1000' UNION ALL

SELECT '270','Jun 3 2009 12:00AM','Customer3','Atex LFJ','1000' UNION ALL

SELECT '271','Jun 3 2009 12:00AM','Customer4','Albewash DNS','500' UNION ALL

SELECT '272','Jun 3 2009 12:00AM','Customer5','Productjet S','500' UNION ALL

SELECT '273','Jun 3 2009 12:00AM','Customer1','Product INX','200' UNION ALL

SELECT '274','Jun 3 2009 12:00AM','Customer1','Product LF','200' UNION ALL

SELECT '275','Jun 3 2009 12:00AM','Customer2','Softex AMP','1000' UNION ALL

SELECT '276','Jun 3 2009 12:00AM','Customer2','Albelene 5X','250' UNION ALL

SELECT '277','Jun 3 2009 12:00AM','Customer2','Product BI','400' UNION ALL

SELECT '278','Jun 3 2009 12:00AM','Customer3','Product SP','50' UNION ALL

SELECT '279','Jun 3 2009 12:00AM','Customer4','Albecid HSB','50' UNION ALL

SELECT '280','Jun 3 2009 12:00AM','Customer5','Product LDG','50' UNION ALL

SELECT '281','Jun 3 2009 12:00AM','Customer1','Product AFW','1000' UNION ALL

SELECT '282','Jun 3 2009 12:00AM','Customer1','Product AFW','800' UNION ALL

SELECT '283','Jun 3 2009 12:00AM','Customer2','Product P','500' UNION ALL

SELECT '284','Jun 3 2009 12:00AM','Customer2','Product ACL 100 R','10' UNION ALL

SELECT '285','Jun 3 2009 12:00AM','Customer2','Product CMF','100' UNION ALL

SELECT '286','Jun 3 2009 12:00AM','Customer3','Product SMJS','500' UNION ALL

SELECT '287','Jun 3 2009 12:00AM','Customer4','Product SBAG','400' UNION ALL

SELECT '288','Jun 3 2009 12:00AM','Customer5','Albefix FFC','250' UNION ALL

SELECT '289','Jun 3 2009 12:00AM','Customer1','Productlon EPN','500' UNION ALL

SELECT '290','Jun 24 2009 12:00AM','Customer1','Productdet WAD','1000' UNION ALL

SELECT '291','Jun 24 2009 12:00AM','Customer2','Albelene 5X','250' UNION ALL

SELECT '292','Jun 24 2009 12:00AM','Customer2','Productlon AC','100' UNION ALL

SELECT '293','Jun 24 2009 12:00AM','Customer2','Product CSD','50' UNION ALL

SELECT '294','Jun 24 2009 12:00AM','Customer3','Product SSG','50' UNION ALL

SELECT '295','Jun 3 2009 12:00AM','Customer4','Product ASA','100' UNION ALL

SELECT '296','Jun 3 2009 12:00AM','Customer5','Product DAI','50' UNION ALL

SELECT '297','Jun 3 2009 12:00AM','Customer1','Product NF 30','50' UNION ALL

SELECT '298','Jun 24 2009 12:00AM','Customer1','Product NS 100','50' UNION ALL

SELECT '299','Jun 24 2009 12:00AM','Customer2','Product C 100 W','50' UNION ALL

SELECT '300','Jun 24 2009 12:00AM','Customer2','Product BI','200' UNION ALL

SELECT '301','Jul 24 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '302','Jul 24 2009 12:00AM','Customer3','Product ECNF','100' UNION ALL

SELECT '303','Jul 24 2009 12:00AM','Customer4','Product ECNF','100' UNION ALL

SELECT '304','Jul 24 2009 12:00AM','Customer5','Product ECNF','100' UNION ALL

SELECT '305','Jul 25 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '306','Jul 25 2009 12:00AM','Customer1','Product ECNF','100' UNION ALL

SELECT '307','Jul 25 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '308','Jul 25 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '309','Jul 25 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '310','Jul 3 2009 12:00AM','Customer3','Product ECNF','100' UNION ALL

SELECT '311','Jul 3 2009 12:00AM','Customer4','Product KAT S','1250' UNION ALL

SELECT '312','Jul 25 2009 12:00AM','Customer5','Product ASA','1000' UNION ALL

SELECT '313','Jul 25 2009 12:00AM','Customer1','Treat 100','200' UNION ALL

SELECT '314','Jul 25 2009 12:00AM','Customer1','Product DAI','300' UNION ALL

SELECT '315','Jul 25 2009 12:00AM','Customer2','Productdet WAD','1000' UNION ALL

SELECT '316','Jul 25 2009 12:00AM','Customer2','Albenit CAN 50','3000' UNION ALL

SELECT '317','Jul 4 2009 12:00AM','Customer2','Albewash DNS','1000' UNION ALL

SELECT '318','Jul 4 2009 12:00AM','Customer3','Albekil PKL RS','2000' UNION ALL

SELECT '319','Jul 25 2009 12:00AM','Customer4','Product C 100 W','400' UNION ALL

SELECT '320','Jul 25 2009 12:00AM','Customer5','Product INX','300' UNION ALL

SELECT '321','Jul 25 2009 12:00AM','Customer1','Product HBWS','250' UNION ALL

SELECT '322','Jul 25 2009 12:00AM','Customer1','Product HPN','500' UNION ALL

SELECT '323','Jul 4 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '324','Jul 4 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '325','Jul 26 2009 12:00AM','Customer2','Product ECNF','100' UNION ALL

SELECT '326','Jul 26 2009 12:00AM','Customer3','Product ECNF','100' UNION ALL

SELECT '327','Jul 26 2009 12:00AM','Customer4','Product ECNF','100' UNION ALL

SELECT '328','Jul 26 2009 12:00AM','Customer5','Product HPN','500' UNION ALL

SELECT '329','Jul 26 2009 12:00AM','Customer1','Product L 100','20' UNION ALL

SELECT '330','Jul 26 2009 12:00AM','Customer1','Lubicon 100 C','10' UNION ALL

SELECT '331','Jul 5 2009 12:00AM','Customer2','Product DAI','200' UNION ALL

SELECT '332','Jul 5 2009 12:00AM','Customer2','Biozyme A Plus','40' UNION ALL

SELECT '333','Jul 26 2009 12:00AM','Customer2','Product ASA','200' UNION ALL

SELECT '334','Jul 26 2009 12:00AM','Customer3','Product EG','350' UNION ALL

SELECT '335','Jul 26 2009 12:00AM','Customer4','Product C 100 W','150' UNION ALL

SELECT '336','Jul 26 2009 12:00AM','Customer5','Product HPGSR Premium','50' UNION ALL

SELECT '337','Jul 5 2009 12:00AM','Customer1','Product LDG','100' UNION ALL

SELECT '338','Jul 26 2009 12:00AM','Customer1','Product BX','100' UNION ALL

SELECT '339','Jul 26 2009 12:00AM','Customer2','Product CMF','150' UNION ALL

SELECT '340','Jul 26 2009 12:00AM','Customer2','Product EG','50' UNION ALL

SELECT '341','Jul 26 2009 12:00AM','Customer2','Product 100','300' UNION ALL

SELECT '342','Jul 26 2009 12:00AM','Customer3','Product SP PR','500' UNION ALL

SELECT '343','Jul 26 2009 12:00AM','Customer4','Product RD Pr','700' UNION ALL

SELECT '344','Jul 26 2009 12:00AM','Customer5','Product BXN New','200' UNION ALL

SELECT '345','Jul 5 2009 12:00AM','Customer1','Albekil VPK','300' UNION ALL

SELECT '346','Jul 5 2009 12:00AM','Customer1','Product ACL 100','10' UNION ALL

SELECT '347','Jul 27 2009 12:00AM','Customer2','Product SMJS','200' UNION ALL

SELECT '348','Jul 5 2009 12:00AM','Customer2','Product CMF','100' UNION ALL

SELECT '349','Jul 5 2009 12:00AM','Customer2','Product DAI N5','200' UNION ALL

SELECT '350','Jul 27 2009 12:00AM','Customer3','PDL 2000','500' UNION ALL

SELECT '351','Jul 27 2009 12:00AM','Customer4','Product AFW','210' UNION ALL

SELECT '352','Jul 5 2009 12:00AM','Customer5','Product RFA','50' UNION ALL

SELECT '353','Jul 5 2009 12:00AM','Customer1','Product RFA','210' UNION ALL

SELECT '354','Jul 5 2009 12:00AM','Customer1','Product HPSA','500' UNION ALL

SELECT '355','Jul 27 2009 12:00AM','Customer2','Product SDN','1000' UNION ALL

SELECT '356','Jul 29 2009 12:00AM','Customer2','Product HPN','500' UNION ALL

SELECT '357','Jul 6 2009 12:00AM','Customer2','Product DAI P','500' UNION ALL

SELECT '358','Jul 6 2009 12:00AM','Customer3','Product L4T','500' UNION ALL

SELECT '359','Jul 6 2009 12:00AM','Customer4','Product CDL','50' UNION ALL

SELECT '360','Jul 6 2009 12:00AM','Customer5','Product HTX Conc','50' UNION ALL

SELECT '361','Jul 6 2009 12:00AM','Customer1','Product CMF','200' UNION ALL

SELECT '362','Jul 6 2009 12:00AM','Customer1','Perox EPN','300' UNION ALL

SELECT '363','Jul 6 2009 12:00AM','Customer2','Product LF','500' UNION ALL

SELECT '364','Jul 6 2009 12:00AM','Customer2','Product HPGSR Premium','150' UNION ALL

SELECT '365','Jul 6 2009 12:00AM','Customer2','Product MA','200' UNION ALL

SELECT '366','Jul 6 2009 12:00AM','Customer3','Product CDL','200' UNION ALL

SELECT '367','Jul 6 2009 12:00AM','Customer4','Product SNS','50' UNION ALL

SELECT '368','Jul 6 2009 12:00AM','Customer5','Product CMF','100' UNION ALL

SELECT '369','Jul 6 2009 12:00AM','Customer1','Product ARD Pr','250' UNION ALL

SELECT '370','Jul 8 2009 12:00AM','Customer1','Safedye RFL','250' UNION ALL

SELECT '371','Jul 8 2009 12:00AM','Customer2','Lubricol ZB','1000' UNION ALL

SELECT '372','Jul 8 2009 12:00AM','Customer2','Product AO','50' UNION ALL

SELECT '373','Jul 8 2009 12:00AM','Customer2','Product HPN','1000' UNION ALL

SELECT '374','Jul 8 2009 12:00AM','Customer3','Product RFL','500' UNION ALL

SELECT '375','Jul 8 2009 12:00AM','Customer4','Nanosil Super','3450' UNION ALL

SELECT '376','Jul 8 2009 12:00AM','Customer5','Product CA','500' UNION ALL

SELECT '377','Jul 8 2009 12:00AM','Customer1','Product FR','500' UNION ALL

SELECT '378','Jul 8 2009 12:00AM','Customer1','Primaproof VFS 50%','700' UNION ALL

SELECT '379','Jul 8 2009 12:00AM','Customer2','Product BI','2000' UNION ALL

SELECT '380','Jul 8 2009 12:00AM','Customer2','Product HPN','500' UNION ALL

SELECT '381','Jul 8 2009 12:00AM','Customer2','Product ECNF','200' UNION ALL

SELECT '382','Jul 8 2009 12:00AM','Customer3','Product ECNF','200' UNION ALL

SELECT '383','Jul 8 2009 12:00AM','Customer4','Product ECNF','200' UNION ALL

SELECT '384','Jul 8 2009 12:00AM','Customer5','Product ECNF','200' UNION ALL

SELECT '385','Jul 8 2009 12:00AM','Customer1','Product ECNF','200' UNION ALL

SELECT '386','Jul 9 2009 12:00AM','Customer1','Product ECNF','200' UNION ALL

SELECT '387','Jul 9 2009 12:00AM','Customer2','Product ECNF','200' UNION ALL

SELECT '388','Jul 9 2009 12:00AM','Customer2','Product ECNF','200' UNION ALL

SELECT '389','Jul 9 2009 12:00AM','Customer2','Product ECNF','200' UNION ALL

SELECT '390','Jul 9 2009 12:00AM','Customer3','Product ECNF','200' UNION ALL

SELECT '391','Jul 9 2009 12:00AM','Customer4','Product P','1500' UNION ALL

SELECT '392','Jul 9 2009 12:00AM','Customer5','Product TT','50' UNION ALL

SELECT '393','Jul 9 2009 12:00AM','Customer1','Product SNS','300' UNION ALL

SELECT '394','Jul 9 2009 12:00AM','Customer1','ProductAlbex BI','100' UNION ALL

SELECT '395','Jul 9 2009 12:00AM','Customer2','Productjet LF','100' UNION ALL

SELECT '396','Jul 9 2009 12:00AM','Customer2','Product EG','50' UNION ALL

SELECT '397','Jul 9 2009 12:00AM','Customer2','Treat 100','500' UNION ALL

SELECT '398','Jul 9 2009 12:00AM','Customer3','Product HPN','300' UNION ALL

SELECT '399','Jul 9 2009 12:00AM','Customer4','Product KAT S','3500' UNION ALL

SELECT '400','Jul 9 2009 12:00AM','Customer5','Product SBAG','100' UNION ALL

SELECT '401','Aug 9 2009 12:00AM','Customer1','Product LF','200' UNION ALL

SELECT '402','Aug 9 2009 12:00AM','Customer1','Lubricol ZB','100' UNION ALL

SELECT '403','Aug 9 2009 12:00AM','Customer2','Atex LFJ','1000' UNION ALL

SELECT '404','Aug 9 2009 12:00AM','Customer2','Product WAD Premium','500' UNION ALL

SELECT '405','Aug 9 2009 12:00AM','Customer2','Product FSI Premium','1000' UNION ALL

SELECT '406','Aug 9 2009 12:00AM','Customer3','Product LF','400' UNION ALL

SELECT '407','Aug 9 2009 12:00AM','Customer4','Product CA','500' UNION ALL

SELECT '408','Aug 9 2009 12:00AM','Customer5','Product EG','250' UNION ALL

SELECT '409','Aug 9 2009 12:00AM','Customer1','Product PEARL','400' UNION ALL

SELECT '410','Aug 9 2009 12:00AM','Customer1','Product APN','300' UNION ALL

SELECT '411','Aug 9 2009 12:00AM','Customer2','Product ASA','200' UNION ALL

SELECT '412','Aug 10 2009 12:00AM','Customer2','Product NF 50','200' UNION ALL

SELECT '413','Aug 10 2009 12:00AM','Customer2','Product RDL','100' UNION ALL

SELECT '414','Aug 10 2009 12:00AM','Customer3','Product AR ECO','500' UNION ALL

SELECT '415','Aug 10 2009 12:00AM','Customer4','Oxysul PRB Liquid','100' UNION ALL

SELECT '416','Aug 10 2009 12:00AM','Customer5','Product SBAG','500' UNION ALL

SELECT '417','Aug 10 2009 12:00AM','Customer1','Product PV','100' UNION ALL

SELECT '418','Aug 10 2009 12:00AM','Customer1','Product RDL','100' UNION ALL

SELECT '419','Aug 10 2009 12:00AM','Customer2','Product CPD','100' UNION ALL

SELECT '420','Aug 10 2009 12:00AM','Customer2','Product CWS','50' UNION ALL

SELECT '421','Aug 10 2009 12:00AM','Customer2','Product U 196','100' UNION ALL

SELECT '422','Aug 10 2009 12:00AM','Customer3','Product ARM 100','500' UNION ALL

SELECT '423','Aug 10 2009 12:00AM','Customer4','Product C 100 W','600' UNION ALL

SELECT '424','Aug 10 2009 12:00AM','Customer5','Synthocat 100','50' UNION ALL

SELECT '425','Aug 10 2009 12:00AM','Customer1','Product 100','100' UNION ALL

SELECT '426','Aug 11 2009 12:00AM','Customer1','Product AS 1','100' UNION ALL

SELECT '427','Aug 11 2009 12:00AM','Customer2','Treat 100','100' UNION ALL

SELECT '428','Aug 11 2009 12:00AM','Customer2','Product HBWS','200' UNION ALL

SELECT '429','Aug 11 2009 12:00AM','Customer2','Productlon AC','100' UNION ALL

SELECT '430','Aug 11 2009 12:00AM','Customer3','Product EDNI','100' UNION ALL

SELECT '431','Aug 11 2009 12:00AM','Customer4','Product DAN','200' UNION ALL

SELECT '432','Aug 11 2009 12:00AM','Customer5','Product PBI','300' UNION ALL

SELECT '433','Aug 11 2009 12:00AM','Customer1','Product Resin','5' UNION ALL

SELECT '434','Aug 11 2009 12:00AM','Customer1','Product RWL','5' UNION ALL

SELECT '435','Aug 11 2009 12:00AM','Customer2','Product BX','5' UNION ALL

SELECT '436','Aug 11 2009 12:00AM','Customer2','Product EWO S','5' UNION ALL

SELECT '437','Aug 11 2009 12:00AM','Customer2','Albefix FFC','5' UNION ALL

SELECT '438','Aug 11 2009 12:00AM','Customer3','Soprint HLA Premium','2' UNION ALL

SELECT '439','Aug 11 2009 12:00AM','Customer4','Treat 100','1' UNION ALL

SELECT '440','Aug 11 2009 12:00AM','Customer5','Product EG','1' UNION ALL

SELECT '441','Aug 12 2009 12:00AM','Customer1','Product NIX H/C','5' UNION ALL

SELECT '442','Aug 12 2009 12:00AM','Customer1','Albepet LFL','10' UNION ALL

SELECT '443','Aug 12 2009 12:00AM','Customer2','Albelase HX 50 Z','10' UNION ALL

SELECT '444','Aug 12 2009 12:00AM','Customer2','Albenit CAN 50','10' UNION ALL

SELECT '445','Aug 12 2009 12:00AM','Customer2','Product CO','5' UNION ALL

SELECT '446','Aug 12 2009 12:00AM','Customer3','Product PMF','5' UNION ALL

SELECT '447','Aug 12 2009 12:00AM','Customer4','Product AD','5' UNION ALL

SELECT '448','Aug 12 2009 12:00AM','Customer5','Product HPAD','5' UNION ALL

SELECT '449','Aug 12 2009 12:00AM','Customer1','Product SWP Conc','5' UNION ALL

SELECT '450','Aug 12 2009 12:00AM','Customer1','Product ECNF','5' UNION ALL

SELECT '451','Aug 12 2009 12:00AM','Customer2','Albecid SVB','5' UNION ALL

SELECT '452','Aug 12 2009 12:00AM','Customer2','Albecid HSB','5' UNION ALL

SELECT '453','Aug 12 2009 12:00AM','Customer2','Product SWA','2' UNION ALL

SELECT '454','Aug 12 2009 12:00AM','Customer3','Safedye RFL','5' UNION ALL

SELECT '455','Aug 12 2009 12:00AM','Customer4','Albecid HSB','5' UNION ALL

SELECT '456','Aug 29 2009 12:00AM','Customer5','Product FR','2' UNION ALL

SELECT '457','Aug 29 2009 12:00AM','Customer1','Product WRC','2' UNION ALL

SELECT '458','Aug 29 2009 12:00AM','Customer1','Product L 100','1' UNION ALL

SELECT '459','Aug 29 2009 12:00AM','Customer2','Product RWL','10' UNION ALL

SELECT '460','Aug 12 2009 12:00AM','Customer2','Product NF 30','4' UNION ALL

SELECT '461','Aug 12 2009 12:00AM','Customer2','Product RWL','5' UNION ALL

SELECT '462','Aug 12 2009 12:00AM','Customer3','Product CDL','4' UNION ALL

SELECT '463','Aug 29 2009 12:00AM','Customer4','Product DAI','4' UNION ALL

SELECT '464','Aug 12 2009 12:00AM','Customer5','Albecid HSB','6' UNION ALL

SELECT '465','Aug 29 2009 12:00AM','Customer1','Albecid BUFF','6' UNION ALL

SELECT '466','Aug 29 2009 12:00AM','Customer1','Product CZ','5' UNION ALL

SELECT '467','Aug 12 2009 12:00AM','Customer2','Product J Spl','5' UNION ALL

SELECT '468','Aug 29 2009 12:00AM','Customer2','Soburst 200','1' UNION ALL

SELECT '469','Aug 29 2009 12:00AM','Customer2','Product FFC','1' UNION ALL

SELECT '470','Aug 12 2009 12:00AM','Customer3','Product RRW','1' UNION ALL

SELECT '471','Aug 12 2009 12:00AM','Customer4','Soburst 200','1' UNION ALL

SELECT '472','Aug 29 2009 12:00AM','Customer5','Product XLHV','1' UNION ALL

SELECT '473','Aug 13 2009 12:00AM','Customer1','Albecid SVB','1' UNION ALL

SELECT '474','Aug 29 2009 12:00AM','Customer1','Product SDBL','1' UNION ALL

SELECT '475','Aug 29 2009 12:00AM','Customer2','Product ECNF','1' UNION ALL

SELECT '476','Aug 29 2009 12:00AM','Customer2','Product CWS','1' UNION ALL

SELECT '477','Aug 13 2009 12:00AM','Customer2','Product AR ECO','1' UNION ALL

SELECT '478','Aug 30 2009 12:00AM','Customer3','Product CWS','1' UNION ALL

SELECT '479','Aug 30 2009 12:00AM','Customer4','Product U Plus','1' UNION ALL

SELECT '480','Aug 30 2009 12:00AM','Customer5','Product NAS','2' UNION ALL

SELECT '481','Aug 30 2009 12:00AM','Customer1','Product RWO','1' UNION ALL

SELECT '482','Aug 30 2009 12:00AM','Customer1','Product PRS','1' UNION ALL

SELECT '483','Aug 30 2009 12:00AM','Customer2','Safedye RFL','1' UNION ALL

SELECT '484','Aug 13 2009 12:00AM','Customer2','Product HLA PREMIUM','1' UNION ALL

SELECT '485','Aug 13 2009 12:00AM','Customer2','Product L4T','1' UNION ALL

SELECT '486','Aug 13 2009 12:00AM','Customer3','Product P','10' UNION ALL

SELECT '487','Aug 13 2009 12:00AM','Customer4','Product AP','5' UNION ALL

SELECT '488','Aug 13 2009 12:00AM','Customer5','Product P','5' UNION ALL

SELECT '489','Aug 30 2009 12:00AM','Customer1','Treat 100','3' UNION ALL

SELECT '490','Aug 30 2009 12:00AM','Customer1','Product SDN','5' UNION ALL

SELECT '491','Aug 30 2009 12:00AM','Customer2','Product NIP','10' UNION ALL

SELECT '492','Aug 15 2009 12:00AM','Customer2','Product MW','5' UNION ALL

SELECT '493','Aug 15 2009 12:00AM','Customer2','Product NF SPL','5' UNION ALL

SELECT '494','Aug 15 2009 12:00AM','Customer3','Product N','5' UNION ALL

SELECT '495','Aug 15 2009 12:00AM','Customer4','Product KBI','15' UNION ALL

SELECT '496','Aug 15 2009 12:00AM','Customer5','Product C 100 W','3' UNION ALL

SELECT '497','Aug 15 2009 12:00AM','Customer1','Primalose TM','3' UNION ALL

SELECT '498','Aug 15 2009 12:00AM','Customer1','Albecid SVB','5' UNION ALL

SELECT '499','Aug 15 2009 12:00AM','Customer2','Product DJ Premium','5' UNION ALL

SELECT '500','Aug 15 2009 12:00AM','Customer2','Product HPDLP','5'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable OFF

Can you please help me write the query.

Regards,

Vaibhav

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Thank you for providing the test data, really well done!

This should work for you:

DECLARE @pivot nvarchar(500)

SET @pivot = ''

SELECT @pivot = @pivot + '[' + Month + '], '

FROM (

SELECT DISTINCT LEFT(CONVERT(char(8), DateValue, 112),6) AS Month

FROM #MyTable

WHERE CustomerName = 'Customer1'

) AS data

SET @pivot = LEFT(@pivot, LEN(@pivot) -1)

DECLARE @sql nvarchar(4000)

SET @sql = N'

SELECT Product, ' + @pivot + '

FROM (

SELECT LEFT(CONVERT(char(8), DateValue, 112),6) AS Month, Product, Quantity

FROM #MyTable

WHERE CustomerName = ''Customer1''

) AS src

PIVOT (

SUM(Quantity) FOR Month IN ('+ @pivot +')

) AS pvt

'

EXEC sp_executesql @sql

Unfortunately, PIVOT doesn't allow dynamic columns, so the only way to make it dynamic is creating the SQL string in code.

Hope this helps

Gianluca

-- Gianluca Sartori

c.vaibhav SSChasing Mays Points: 652 More actions · Answer 4

Thanks Gianluca.

Few questions:

1) Instead of #MyTable I need to put a query which joins two tables. I actually have two tables which contains the required data.

2) I want the user to select the "From Month" and "To Month". Where do I give that provision in the query.

Thanks a lot!!!

Regards,

Vaibhav

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

You can change the code this way, just replace the FirstTable/SecondTable with your table names:

-- These variables could be your SP parameters:

DECLARE @StartDate datetime

DECLARE @EndDate datetime

DECLARE @CustomerName nvarchar(100)

SET @StartDate = '20090501'

SET @EndDate = '20090930'

SET @CustomerName = 'Customer1'

DECLARE @pivot nvarchar(500)

SET @pivot = ''

SELECT @pivot = @pivot + '[' + Month + '], '

FROM (

SELECT DISTINCT LEFT(CONVERT(char(8), DateValue, 112),6) AS Month

FROM FirstTable

INNER JOIN SecondTable

ON FirstTable.Column1 = SecondTable.Column1

WHERE CustomerName = @CustomerName

AND DateValue BETWEEN @StartDate AND @EndDate

) AS data

SET @pivot = LEFT(@pivot, LEN(@pivot) -1)

DECLARE @sql nvarchar(4000)

SET @sql = N'

WITH CTE (CustomerName, DateValue, Product, Quantity)

AS (

SELECT CustomerName, DateValue, Product, Quantity

FROM FirstTable

INNER JOIN SecondTable

ON FirstTable.Column1 = SecondTable.Column1

WHERE DateValue BETWEEN @StartDate AND @EndDate

)

SELECT Product, ' + @pivot + '

FROM (

SELECT LEFT(CONVERT(char(8), DateValue, 112),6) AS Month, Product, Quantity

FROM CTE

WHERE CustomerName = @CustomerName

) AS src

PIVOT (

SUM(Quantity) FOR Month IN ('+ @pivot +')

) AS pvt

'

EXEC sp_executesql @sql, N'@StartDate datetime, @EndDate datetime, @CustomerName nvarchar(100)', @StartDate, @EndDate, @CustomerName

Hope this is what you were looking for.

-- Gianluca Sartori