CODEWITHSUNDEEP
sqlsql-server
user2837480
user2837480

Reputation: 369

Repeat all columns and data for all the resulting rows

Assume I have below table 

CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Price] [int] NOT NULL,
[Qty] [int] NOT NULL)

With a select query, I can get the below:

Name      Price    Qty
NIKE       600     1
ADIDAS     500     2
Skechers   400     5
FILA       250    10

But I want a result like below.

Name    Price    Qty  Name    Price    Qty  Name      Price    Qty  Name    Price    Qty
NIKE     600     1    ADIDAS   500     2    Skechers   400     5    FILA      250    10

Upvotes: 0

Views: 49

Answers (1)

Thom A
Thom A

Reputation: 95554

Well, this is ugly, and I still think this is an XY Problem, but it does the job you asked for...:

USE Sandbox;
GO
--Create sample table
CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL, --Do you REALLY need 2GB to store the NAME of a product? What name is going to be 2,000,000,000 character in length..?
[Price] [int] NOT NULL,
[Qty] [int] NOT NULL)
GO
--Insert sample data
INSERT INTO dbo.Product (Name,
                     Price,
                     Qty)
VALUES('NIKE    ',600, 1),
      ('ADIDAS  ',500, 2),
      ('Skechers',400, 5),
      ('FILA    ',250,10);
GO
--Solution
DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = N'SELECT ' +
           STUFF((SELECT N',' + @CRLF +
                         N'       MAX(CASE ID WHEN ' + QUOTENAME(ID,'''') + N' THEN [Name] END) AS [Name],' + @CRLF +
                         N'       MAX(CASE ID WHEN ' + QUOTENAME(ID,'''') + N' THEN Price END) AS Price,' + @CRLF +
                         N'       MAX(CASE ID WHEN ' + QUOTENAME(ID,'''') + N' THEN Qty END) AS Qty'
                  FROM dbo.Product
                  ORDER BY ID ASC
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,10,N'') + @CRLF +
           N'FROM dbo.Product;';

PRINT @SQL; --Your Best Friend

EXEC sp_executesql @SQL;

GO
--Clean up
--DROP TABLE dbo.Product;

Upvotes: 2

Related Questions