July 15, 2013 at 9:04 am
Struggling with creating a dynamic pivot query. Below is a snippet of my data that I am working with and trying to come up with a query.
Data Structure:
itemnum itempagenum filepath
1993895 0 \V1\1\101.TIF
1993895 1 \V1\1\102.TIF
1993895 2 \V1\1\103.TIF
1993895 3 \V1\1\104.TIF
1993896 0 \V1\1\105.TIF
1993896 1 \V1\1\106.TIF
1993897 0 \V1\1\107.TIF
1993897 1 \V1\1\108.TIF
1993897 2 \V1\1\109.TIF
1993897 3 \V1\1\110.TIF
Here is the query I started with but not sure how to turn the filepathN into a dynamic column because the itempagenum has a range of 0 to 800 and I know I don't want to hard-code the column names.
;WITH CTE_RowNum
AS(
select itemnum, filepath,
ROW_NUMBER() OVER(Partition by itemnum ORDER BY itemnum,itempagenum) as row
from hsi.itemdatapage t)
--INSERT INTO stageTable
SELECT itemnum, [1] as filepath, [2] as filepath2, [3] as filepath3, [4] as filepath4, [5] as filepath5, [6] as filepath6, [7] as filepath7, [8] as filepath8, [9] as filepath9, [10] as filepath10, [11] as filepath11, [12] as filepath12, [13] as filepath13, [14] as filepath14, [15] as filepath15
FROM (
SELECT itemnum, filepath,row
FROM CTE_RowNum
) p
PIVOT (
MAX (filepath)
FOR row IN
( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])
) AS pvt
My table is around 700k records. Thanks for the assist!
July 15, 2013 at 9:35 am
Take a look at the link in my signature about dynamic cross tabs. It will walk you through how to do dynamic versions of this type of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2013 at 10:00 am
Thanks Sean that's what I was needing!
July 15, 2013 at 10:02 am
You're welcome. Glad that got you going.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply