Over a million developers have joined DZone.

TSQL Trick only with FOR XML Support

· Java Zone

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

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


Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

Topics:

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}