question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked EchoLiu-msft commented

How To Double Unpivot

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
sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is the output you expected?

0 Votes 0 ·

It's difficult to make sense of your code or what you are saying.

so I get the name, the items, and the count of the items?"

What names, items and counts? Please bear in mind that while know the data you are working with, we do not. As Guoxiong says, you need to clarify which result you want.

0 Votes 0 ·

Do you have any updates?

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @InigoMontoya-1790,

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.




image.png (3.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.