Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Cross Tab or Pivot

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 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