?
Solved

SQL Cross Tab or Pivot

Posted on 2012-03-26
5
Medium Priority
?
1,128 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
  • 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 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