How To Double Unpivot

Question

I am trying to take columns to rows so that I get one row for each person in the table (my sample data only has one row). I tried the below DDL but I get multiple errors. What is the proper way to use TWO Unpivots so I get the name, the items, and the count of the items?

CREATE TABLE [dbo].[___Test](
    [Name] [nvarchar](50) NULL,
    [Item1] [nvarchar](50) NULL,
    [Item2] [nvarchar](50) NULL,
    [Item3] [nvarchar](50) NULL,
    [Item4] [nvarchar](50) NULL,
    [Item5] [nvarchar](50) NULL,
    [Item6] [nvarchar](50) NULL,
    [Item7] [nvarchar](50) NULL,
    [Item8] [int] NULL,
    [Item9] [nvarchar](50) NULL,
    [Item10] [nvarchar](50) NULL,
    [Item11] [nvarchar](50) NULL,
    [Item12] [nvarchar](50) NULL,
    [Item13] [nvarchar](50) NULL,
    [Item14] [nvarchar](50) NULL,
    [Item15] [nvarchar](50) NULL,
    [Item16] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[___Test] ([Name], [Item1], [Item2], [Item3], [Item4], [Item5], [Item6], [Item7], [Item8], [Item9], [Item10], [Item11], [Item12], [Item13], [Item14], [Item15], [Item16]) VALUES (N'Accomac', NULL, NULL, N'10', N'5', NULL, NULL, NULL, 5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
GO


  Select [Name], [Item], Qty
  From [___Test]
  UNPIVOT
  (
    Item For Items IN ([Item1]
      ,[Item2]
      ,[Item3]
      ,[Item4]
      ,[Item5]
      ,[Item6]
      ,[Item7]
      ,[Item8]
      ,[Item9]
      ,[Item10]
      ,[Item11]
      ,[Item12]
      ,[Item13]
      ,[Item14]
      ,[Item15]
      ,[Item16])
  ) As P
  UNPIVOT
  (
    QTY For Quantities IN ([Item1]
      ,[Item2]
      ,[Item3]
      ,[Item4]
      ,[Item5]
      ,[Item6]
      ,[Item7]
      ,[Item8]
      ,[Item9]
      ,[Item10]
      ,[Item11]
      ,[Item12]
      ,[Item13]
      ,[Item14]
      ,[Item15]
      ,[Item16])
  ) As PT

Answer

Hi @Johnathan Simpson ,

As everyone said, please provide the expected output of your test data. Perhaps the following is what you want:

;With cte as(  
Select *  
From (Select * From [___Test]) t  
   UNPIVOT  
   (  
     Item For Items IN ([Item1] ,[Item2],[Item3],[Item4],[Item5]  
       ,[Item6] ,[Item7],[Item8],[Item9],[Item10],[Item11],[Item12]  
       ,[Item13],[Item14],[Item15],[Item16])  
   ) As P)  
  
Select *,Count(Items) Over(partition by name) qty  
From cte

Output:

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Share via

How To Double Unpivot

1 answer