stored procedure rows to columns

Posted on 2013-01-09
Medium Priority
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
Question by:hongclub
  • 4
  • 2
  • 2
  • +1
LVL 74

Accepted Solution

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
ORDER BY col1;
LVL 12

Expert Comment

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)
pivot (max(col3) for col2 in ([A],[B],[C])) as b

Open in new window


Author Comment

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  


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)
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!


Author Comment

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

Expert Comment

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
LVL 74

Assisted Solution

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;
LVL 72

Expert Comment

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

Not enough information to confirm an answer.
LVL 74

Expert Comment

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
LVL 72

Expert Comment

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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

597 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