Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

TSQL Trick only with FOR XML Support

DZone's Guide to

TSQL Trick only with FOR XML Support

· Java Zone ·
Free Resource

Download Microservices for Java Developers: A hands-on introduction to frameworks and containers. Brought to you in partnership with Red Hat.

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 Past


Download Building Reactive Microservices in Java: Asynchronous and Event-Based Application Design. Brought to you in partnership with Red Hat

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}