SQL Server PIVOT returns only ONE row

Question

SQL Server 2017.

I have the following table:

The following SQL Statement is run in a stored procedure:

Declare @LocationID INT
set @LocationID = 1

DECLARE @Columns NVARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(BayName) FROM (SELECT Distinct BayName 
FROM ticketbaylist WHERE LocationID = @LocationID) as a ORDER BY BayName

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT ' + @Columns + ' FROM (SELECT TILE, BayName
FROM TicketBayList WHERE LocationID = ' + CAST(@LocationID AS NVARCHAR(3)) + ') as PivotData PIVOT (MAX(TILE) 
FOR BayName IN (' + @Columns + ')) as PIVOTResult'

EXEC (@SQL)

I get back ONLY one row. There should be several. Here is a screenshot of expected results that I had to hard-code. Anyone have any ideas?

did you try printing out the @SQL variable before you EXEC it? I'm guessing you will see the problem as soon as you do that. Then go look at the documentation for STRING_AGG — Hogan
– Hogan, Commented Apr 22, 2020 at 18:09
You are pivoting the data and limiting it to a single location, so you will only get back one row. Are you trying to show all locations, pivoted? — Aaron Hughes
– Aaron Hughes, Commented Apr 22, 2020 at 18:42
The pivot is on the bay column. Location 1 has several bays and each bay has more than one entry. So I would still expect several rows. — JamesNT
– JamesNT, Commented Apr 22, 2020 at 19:02
That's not how pivot's work. Pivot's require an aggregate of some sort, so you'll only get one value and one row per column you pivot off of. You may consider concatenating the entries if you need them all. — Aaron Hughes
– Aaron Hughes, Commented Apr 22, 2020 at 19:17
Correct. Max is just that. A Single, Maximum value. If you need multiple rows, you'll have to find something else to pivot on, or concatenate the records together. Although, i'd consider handling this at the presentation layer. — Aaron Hughes
– Aaron Hughes, Commented Apr 28, 2020 at 16:55

Aaron Hughes · Accepted Answer · 2020-04-30 14:54:03Z

Alright, it's not the prettiest thing in the world, but since you've already committed to using dynamic SQL, this should get the job done. I tried to run just the initial pivot, but I couldn't get the results to roll up. They ended up looking like this:

Close, but not exactly what you are after. So here is the what I came up with to extend on this. There may be (and I hope there is) a cleaner way to do this, but here goes nothing:

--DEMO SETUP
DROP TABLE IF EXISTS #ticketbaylist

CREATE TABLE #ticketbaylist
(
    ID INT IDENTITY (1,1) NOT NULL
    ,Tile NVARCHAR(250)
    ,BayName NVARCHAR(20)
    ,LocationID INT
)

INSERT INTO #ticketbaylist
VALUES
('Ticket 1: test 1', 'Bay 02', 1)
,('Ticket 2: test 2', 'Bay 02', 2)
,('Ticket 3: test 3', 'Bay 02', 3)
,('Ticket 4: test 4', 'Bay 01', 2)
,('Ticket 5: test 5', 'Bay 01', 2)
,('Ticket 6: test 6', 'Bay 02', 1)
,('Ticket 7: test 7', 'Bay 05', 1)
,('Ticket 8: test 8', 'Bay 09', 1)
,('Ticket 9: test 9', 'Bay 09', 1)
,('Ticket 10: test 10', 'Bay 05', 1)
,('Ticket 11: test 11', 'Bay 05', 1)
,('Ticket 12: test 12', 'Bay 04', 1)
,('Ticket 13: test 13', 'Bay 09', 1)
,('Ticket 14: test 14', 'Bay 03', 1)
,('Ticket 15: test 15', 'Bay 03', 1)

--END DEMO SETUP

DECLARE @Columns NVARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(BayName) FROM (SELECT Distinct BayName 
FROM #ticketbaylist) as a ORDER BY BayName

DECLARE @LocationID INT = 1
DECLARE @ticketEntries NVARCHAR(MAX) = N''
DECLARE @locationBays NVARCHAR(MAX) = N''
DECLARE @locationBaysUpdate NVARCHAR(MAX) = N''
DECLARE @lbColumnDefs NVARCHAR(MAX) = N''
DECLARE @sql NVARCHAR(MAX) = N''

SELECT @locationBays += ',' + QUOTENAME(BayName) FROM #ticketbaylist GROUP BY BayName
SELECT @locationBaysUpdate += ',' + QUOTENAME(BayName) + ' = b.' + QUOTENAME(BayName) FROM #ticketbaylist GROUP BY BayName
SELECT @lbColumnDefs += ',' + QUOTENAME(BayName) + ' NVARCHAR(MAX)' FROM #ticketbaylist GROUP BY BAYNAME

SELECT @sql = 'DECLARE @outputTable TABLE
(
    TicketEntry INT'
+ @lbColumnDefs
+ ')

;WITH pvtSetup
AS
(
    SELECT *
    ,ROW_NUMBER() OVER (PARTITION BY LocationID, BayName ORDER BY ID) as BayEntry
    FROM #ticketbaylist
)

SELECT 
    BayEntry,' + @Columns
+ ' into #pvt
 FROM pvtSetup
PIVOT (
        MIN(tile)
        FOR BayName IN (' + @Columns + ')
     ) as pvt

INSERT INTO @outputTable (TicketEntry) SELECT DISTINCT BayEntry FROM #pvt


 UPDATE @outputTable
 SET ' + STUFF(@locationBaysUpdate, 1,1,'') +
 ' FROM @outputTable a
        inner join #pvt b on a.TicketEntry = b.BayEntry

 SELECT ' + STUFF(@locationBays, 1,1,'') + ' FROM @outputTable'

PRINT @sql
EXEC (@sql)

And here is what that query produces:

I'll check it out this weekend. Under the gun right now. Thank you for your help in advanced.

Collectives™ on Stack Overflow

SQL Server PIVOT returns only ONE row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related