Solved

stored procedure rows to columns

Posted on 2013-01-09
11
32 Views
Last Modified: 2016-01-28
i have a table looks like that (notes, actually i have more columns, but it is a simplified version)

1  A  10
1  B  3
2  A  9
2  C  40

How can i convert them to
     A    B    C
1  10   3
2  9         40
0
Comment
Question by:hongclub
  • 4
  • 2
  • 2
  • +1
11 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
SELECT   col1,
         MAX(CASE WHEN col2 = 'A' THEN col3 END) a,
         MAX(CASE WHEN col2 = 'B' THEN col3 END) b,
         MAX(CASE WHEN col2 = 'C' THEN col3 END) c
    FROM yourtable
GROUP BY col1
ORDER BY col1;
0
 
LVL 12

Expert Comment

by:Jared_S
Comment Utility
Depending on the amount of data you have, you may want to use a pivot command.

The syntax will be similar to:

select * from (
select col1, col2, col3 from mytable)
a
pivot (max(col3) for col2 in ([A],[B],[C])) as b

Open in new window

0
 

Author Comment

by:hongclub
Comment Utility
now i twist my data a little bit better and put them into a temp table

How can i make this result set (#result):
A    B     C        dD          dE            dF              dG
test test  test  01/05/2006
test test  test              01/06/2006
test test  test                           01/08/2006
test test  test                                       01/09/2006
test2 test2 test2 06/05/2013
test2 test2 test2             06/06/2013
test2 test2 test2                          06/08/2013
test2 test2 test2                                          06/09/2013  


to

A     B     C                 dD                    dE                     dF                     dG
test  test  test               01/05/2006      01/06/2006      01/08/2006      01/09/2006
test2 test2 test2        06/05/2013      06/06/2013      06/08/2013      06/09/2013


(notes: dD, dE .... are dynamic, but consider it as statis first to make it easier)
0
 

Author Comment

by:hongclub
Comment Utility
you think i should do a left join the table itself?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> (notes: dD, dE .... are dynamic, but consider it as statis first to make it easier)

you can't have dynamic columns in SQL.

every column must be known at the time the statement is parsed, not after it executes
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
no, a left join won't really help

but, using your data above, same idea as before, except it's easier because your data is already nulled and spread as needed to make the aggregation work

SELECT   a,b,c,
         MAX(dd) dd,
         MAX(de) de,
         MAX(df) df,
         max(dg) dg
    FROM yourtable
GROUP BY a,b,c
ORDER BY a,b,c;
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
both of the queries I posted above produces the results requested from the data provided

ID: 38760041 -- for the original data and results
ID: 38761058  -- for the extended data and new results
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
The dynamic column part is what might be needed, hence my suggestion. But on the other hand, you answered the original question plus addition ... I'll change my suggestion.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 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

10 Experts available now in Live!

Get 1:1 Help Now