Solved

SQL Cross Tab or Pivot

Posted on 2012-03-26
5
1,034 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 250 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 250 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now