Sorting Dynamically generated columns in order

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sorting Dynamically generated columns in order

Author

Topic

Kobojunkie
Starting Member

8 Posts

Posted - 2012-10-19 : 00:03:48

I have a requirement to generate 15 lines of product detail information for each productID in my Product table, and then convert all 15 intro product detail information into columns for each product id. Not I am able to generate the data quite already however I have am still not able to implement sorting of the data that ensure that (columns be generated in order of the values contained in the columns) For instance, columns that contain NULL should be last in the list while those with values come first.

insert into #PriceSheet
select ID, ProductID,SheetNumber ,SheetDesc ,MfgPriceCode,PriceZone

FROM ProductPrice
left join UNITS On SUOM = UOMID

select @colsUnpivot = stuff((select ','+quotename(C.name)
from tempdb.sys.columns as C
where C.object_id = object_id('tempdb..#PricingSheet') and
C.name LIKE '%%'
for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT ','
+ quotename(c.name
+ cast(t.rn as varchar(10)))
from
(
select row_number() over(partition by ProductID
order by ProductID) rn
from #PriceSheet
) t
cross apply
tempdb.sys.columns as C
where C.object_id = object_id('tempdb..#PricingSheet')
and C.name Not in ('CreateDate', 'LastModifiedDate')
group by c.name, t.rn
order by t.rn
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'select *
from
(
select ProductID, col + cast(rn as varchar(10)) new_col, val
from
(
select
cast(ProductID as varchar(50))ProductID
,cast(SheetNumber as varchar(50))SheetNumber
,cast(SheetDesc as varchar(50))SheetDesc
,cast(MfgPriceCode as varchar(50))MfgPriceCode
,cast(PriceZone as varchar(50))PriceZone
row_number() over(partition by productid order by productid) rn

from #PriceSheet
) x
unpivot
(
val
for col in ('+ @colsunpivot +')
) u
) x1
pivot
(
max(val)
for new_col in
('+ @colspivot +')
) p'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 22:53:08

sorry didnt understand you scenario. can you elaborate with some sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources