Need Better Way of Converting Rows to Columns

Question

Post reply

Need Better Way of Converting Rows to Columns

AeroNC

Old Hand

Points: 314
More actions
December 9, 2011 at 8:17 am

#99937

Can someone please tell me what I am doing wrong? I am using SQL Server 2008 and I am looking for a better way to transpose a table from this........
ListingIDFileName Priority
7700608192754sm.jpg1
7700608192820sm.jpg2
7700608192835sm.jpg3
7700608192848sm.jpg4
7700608192905sm.jpg5
8501008164508sm.jpg1
8501008164522sm.jpg2
8501008164530sm.jpg3
8501008164541sm.jpg4
8501008164619sm.jpg5
To This.....
ListingIDPhoto01Photo02Photo03Photo04Photo05
77... ... ... ... ...
85... ... ... ... ...
I am currently converting it with subqueries using the sql below, but this places a lot of demand on resources and I would like to find a more efficient way of doing it.
Select ListingID,
(Select FileName From Photos Where Priority = 1 and ListingID = listings.ListingID) AS Photo01,
(Select FileName From Photos Where Priority = 2 and ListingID = listings.ListingID) AS Photo02,
(Select FileName From Photos Where Priority = 3 and ListingID = listings.ListingID) AS Photo03,
(Select FileName From Photos Where Priority = 4 and ListingID = listings.ListingID) AS Photo04,
(Select FileName From Photos Where Priority = 5 and ListingID = listings.ListingID) AS Photo05
from Listings
It looks a PIVOT would be the way to go, but I am not having any success. Can someone point me in the right direction?
SELECT ListingID as Listingid, [1] AS 'Photo01', [2] as 'Photo02', [3] as'Photo03', [4] as'Photo04', [5] as'Photo05', [1] AS 'Photo01', [2] as 'Photo02', [3] as'Photo03', [4] as'Photo04', [5] as'Photo05'
FROM
(SELECT ListingID, [FileName], [Priority]
FROM Photos ) AS SourceTable
PIVOT
(
AVG(SourceTable.listingid)
FOR Priority IN ([1], [2], [3], [4], [5])
) AS PivotTable;

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

BEGIN TRAN

--Sample data

SELECT ListingID, FileName, Priority

INTO #listing

FROM (SELECT 77, '00608192754sm.jpg', 1

UNION ALL SELECT 77, '00608192820sm.jpg', 2

UNION ALL SELECT 77, '00608192835sm.jpg', 3

UNION ALL SELECT 77, '00608192848sm.jpg', 4

UNION ALL SELECT 77, '00608192905sm.jpg', 5

UNION ALL SELECT 85, '01008164508sm.jpg', 1

UNION ALL SELECT 85, '01008164522sm.jpg', 2

UNION ALL SELECT 85, '01008164530sm.jpg', 3

UNION ALL SELECT 85, '01008164541sm.jpg', 4

UNION ALL SELECT 85, '01008164619sm.jpg', 5) a(ListingID, FileName, Priority)

--Pivot query

SELECT ListingID, [1] AS Photo01, [2] AS Photo02, [3] AS Photo03,

[4] AS Photo04, [5] AS Photo05

FROM (SELECT ListingID, FileName, Priority

FROM #listing) DataTable

PIVOT (MAX(FileName) FOR Priority IN ([1],[2],[3],[4],[5]) ) AS p

ROLLBACK

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

The method you are using - correlated subqueries - is similar in layout to a cross-tab, but check out the real thing for performance:

SELECT

ListingID,

Photo01 = MAX(CASE WHEN Priority = 1 THEN [FileName] END),

Photo02 = MAX(CASE WHEN Priority = 2 THEN [FileName] END),

Photo03 = MAX(CASE WHEN Priority = 3 THEN [FileName] END),

Photo04 = MAX(CASE WHEN Priority = 4 THEN [FileName] END),

Photo05 = MAX(CASE WHEN Priority = 5 THEN [FileName] END)

FROM Listings

GROUP BY ListingID

Although it's less elegant than Cadavre's code, it's likely to perform a little faster.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

ChrisM@Work (12/9/2011)
Although it's less elegant than Cadavre's code, it's likely to perform a little faster.

Agreed 100%. Generally I use cross-tab queries to pivot data, I showed the syntax for PIVOT because the OP specifically asked for it 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Cadavre (12/9/2011)
ChrisM@Work (12/9/2011)
Although it's less elegant than Cadavre's code, it's likely to perform a little faster.
Agreed 100%. Generally I use cross-tab queries to pivot data, I showed the syntax for PIVOT because the OP specifically asked for it 🙂

Cross-tab queries are a darned sight easier to tweak, too!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

AeroNC Old Hand Points: 314 More actions · Answer 5

Chris,

Thanks alot! That is exactly what I needed. I had tried the CrossTab approach earlier, but forgot to include the MAX statement. Thanks for pointing me in the right direction!!

Graham