SQL Cross Tab or Pivot

I am real close to getting this right. I have a destination that is comprised of 21 columns. The first column is called article and the next 20 are called Fields 01-20. They are all string fields. For each article there could be up to 20 property values. The first 10 potential values are string in the source table and the second 10 potential values are float. In this example, I am looking at 7 potential values which are correctly returned, one on each row. However, I need to pivot these 7 rows into 1 where the row number now indicates the field number column in the destination table. The issue is that for each article the sort order is different and the sort order is defined by the category_id and the records are returned in the proper sequence based on the records in the category table.

Is it possible to convert this to a pivot or cross tab query so the order of the columns matches the order or the rows? For each article I want one line with the potential other 20 fields filled in the sequence that matches the rows.

select article,column_name,
case when cp.column_name = 'var1'    then pr.var1    
      when cp.column_name = 'var2'    then pr.var2
      when cp.column_name = 'var3'    then pr.var3
      when cp.column_name = 'var4'    then pr.var4
      when cp.column_name = 'var5'    then pr.var5
      when cp.column_name = 'var6'    then pr.var6
      when cp.column_name = 'var7'    then pr.var7
      when cp.column_name = 'var8'    then pr.var8
      when cp.column_name = 'var9'    then pr.var9
      when cp.column_name = 'var10'   then pr.var10
      when cp.column_name = 'float1'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float2'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float3'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float4'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float5'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float6'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float7'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float8'  then convert(nvarchar(255),pr.float1)  
      when cp.column_name = 'float9'  then convert(nvarchar(255),pr.float1)
      when cp.column_name = 'float10' then convert(nvarchar(255),pr.float1) else NULL
end as PropText,row_number() over(order by article) as PropID
from new_products pr
inner join new_category_products cp on pr.category_id = cp.category_id
where pr.category_id=59 and article='42011075'
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rwheeler23Author Commented:
I was just about to retire when the idea of using Dynamic SQL to finish this off hit me. For each article I could read the N records containing fields that are applicable. As I loop through the N records I keep appending onto the end of the Dynamics SQL statement. Once I get to the end, I execute the Dynamics SQL statement to insert the one record with N fields and then I move onto the next article. Does this make sense?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
rwheeler23Author Commented:
That is somewhat true. We appeared to be going down the wrong path so I submitted it again in a more defined direction. Please advise if it would have been more correct to append the original question as opposed to opening a new question.

The bottom line question now is can what I am trying to accomplish be done with a cross tab or pivot table query or is the idea of building a Dynamic SQL statment the better alternative?
keyuCommented:
select article,var1,var2,var3,var4,var5,var6,var7,var8,var9,var10,float1,float2,float3,float4,float5,float6,float7,float8,float9,float10,row_number() over(order by article) as PropID
from (select article,column_name from new_products pr
inner join new_category_products cp on pr.category_id = cp.category_id
where pr.category_id=59 and article='42011075') as sourcetable pivot( convert(nvarchar(255),column_name) for column_name in ('var1','var2','var3','var4','var5','var6','var7','var8','var9','var10','float1','float2','float3','float4','float5','float6','float7','float8','float9','float10') as pivottable ORDER BY article;


Note: syntax might be wrong this is just an idea..
rwheeler23Author Commented:
I was never able to get the pivot to work. I got this to work by resorting to Dynamics SQL. I build the list for each item based on the variable number of type properties that existed for each item.

I appreciate everyone's assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.