?
Solved

stored procedure rows to columns

Posted on 2013-01-09
11
Medium Priority
?
40 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
[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
  • 4
  • 2
  • 2
  • +1
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38760041
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
ID: 38760477
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
ID: 38760986
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:hongclub
ID: 38760991
you think i should do a left join the table itself?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38761034
>>> (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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 38761058
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 70

Expert Comment

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

Not enough information to confirm an answer.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41428562
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 70

Expert Comment

by:Qlemo
ID: 41428571
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

801 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