Friday, 4 April 2014

TSQL trick only with FOR XML Support

Introduction

In this article we are going to demonstrate a TSQL trick only with FOR XML Support.

Case Study
We have three Table Objects

tbl_CUSTOMER

CUSTID
CUSTNAME
1
Joydeep Das
2
Chandan Bannerjee
3
Soumen Bhowmik

tbl_ITEMDTLS

ITEMCD
ITEMNAME
100
Tooth Paste
101
Tooth Brusg
102
Saving Lotion
103
Saving Brush

Now the customer purchase Items

tbl_SALEDTLS

SALENO
SRLNO
CUSTID
ITEMCD
201
1
1
100
201
2
1
101
201
3
1
102
201
4
1
103
202
1
2
100
202
2
2
101
203
1
3
100

We want a report like this Format

CUSTID
CUSTNAME
ITEM DETAILS
1
Joydeep Das
 Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
2
Chandan Bannerjee
 Tooth Brusg, Tooth Paste
3
Soumen Bhowmik
 Tooth Paste
1
Joydeep Das
 Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste

How to solve it

-- Table Object Customer
IF OBJECT_ID(N'dbo.tbl_CUSTOMER', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_CUSTOMER];
   END
GO
CREATE TABLE [dbo].[tbl_CUSTOMER]
      (
         CUSTID    INT         NOT NULL IDENTITY PRIMARY KEY,
         CUSTNAME  VARCHAR(50) NOT NULL
      );
GO

-- Insert Records
INSERT INTO [dbo].[tbl_CUSTOMER]
      (CUSTNAME)
VALUES('Joydeep Das'),
      ('Chandan Bannerjee'),
      ('Soumen Bhowmik');                 

-- Table Object Item Details
IF OBJECT_ID(N'dbo.tbl_ITEMDTL', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_ITEMDTL];
   END
GO
CREATE TABLE [dbo].[tbl_ITEMDTL]
   (
     ITEMCD       INT         NOT NULL IDENTITY(100,1) PRIMARY KEY,
     ITEMNAME     VARCHAR(50) NOT NULL
   )
GO

-- Insert Records
INSERT INTO [dbo].[tbl_ITEMDTL] 
    (ITEMNAME)
VALUES('Tooth Paste'),
      ('Tooth Brusg'),
      ('Saving Lotion'),
      ('Saving Brush');
     
-- Table Object Sales Dtls
IF OBJECT_ID(N'dbo.tbl_SALEDTLS', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_SALEDTLS];
   END
GO           
CREATE TABLE [dbo].[tbl_SALEDTLS]
   (
     SALENO   INT   NOT NULL,
     SRLNO    INT   NOT NULL,
     CUSTID   INT   NOT NULL,
     ITEMCD   INT   NOT NULL,
     CONSTRAINT PK_tbl_SALEDTLS PRIMARY KEY
        (
           SALENO ASC,
           SRLNO  ASC
        )
   )           
GO

-- Insert Records
INSERT INTO [dbo].[tbl_SALEDTLS]
      (SALENO, SRLNO, CUSTID, ITEMCD)
VALUES(201, 1, 1, 100),
      (201, 2, 1, 101),
      (201, 3, 1, 102),
      (201, 4, 1, 103),
      (202, 1, 2, 100),
      (202, 2, 2, 101),
      (203, 1, 3, 100);  
     
GO
SELECT * FROM  [dbo].[tbl_CUSTOMER];
SELECT * FROM  [dbo].[tbl_ITEMDTL];
SELECT * FROM  [dbo].[tbl_SALEDTLS]; 

-- Query
SELECT a.CUSTID, a.CUSTNAME,
       STUFF((SELECT ', '+ y.ITEMNAME
              FROM   [dbo].[tbl_SALEDTLS] AS x
                     INNER JOIN [dbo].[tbl_ITEMDTL] AS y
              ON x.ITEMCD = y.ITEMCD
              WHERE  x.CUSTID = a.CUSTID
              ORDER BY ',' + y.ITEMNAME
              FOR XML PATH('')),1,1,'') AS [ITEM DETAILS]
FROM   [dbo].[tbl_CUSTOMER] AS a; 

CUSTID   CUSTNAME                      ITEM DETAILS
1              Joydeep Das                    Saving Brush, Saving Lotion, Tooth Brusg, Tooth Paste
2              Chandan Bannerjee       Tooth Brusg, Tooth Paste
3              Soumen Bhowmik           Tooth Paste



Hope you like it.


Posted by: MR. JOYDEEP DAS