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