?
Solved

SQL Cross Tab or Pivot

Posted on 2012-03-26
5
Medium Priority
?
1,099 Views
Last Modified: 2012-08-13
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'
0
Comment
Question by:rwheeler23
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 

Accepted Solution

by:
rwheeler23 earned 0 total points
ID: 37769594
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?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 37769609
0
 

Author Comment

by:rwheeler23
ID: 37769622
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?
0
 
LVL 9

Assisted Solution

by:keyu
keyu earned 1000 total points
ID: 37770021
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..
0
 

Author Closing Comment

by:rwheeler23
ID: 37805182
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.
0

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question