How To Double Unpivot

Johnathan Simpson 586 Reputation points
2021-08-30T13:56:05.953+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,575 questions
{count} votes

1 answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,576 Reputation points
    2021-08-31T03:14:55.113+00:00

    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:
    127670-image.png

    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.

    0 comments No comments