Using row results as dyamic columns?

Question

Post reply

Using row results as dyamic columns?

matt-988012

SSC Journeyman

Points: 87
More actions
August 20, 2009 at 4:06 am

#135592

edit: quote me to see a clear view on the code fields
Hi there,
I'm looking for the easiest/fastest way to bring a query result like this
Name department percentage
GustavGW 80
GustavNW 20
Olaf D 100
DetlevNW 50
DetlevD 50
into a form like this:
Name GW NW D
Gustav 80 20
Olaf 100
Detlev 50 50
I can only think up a solution with dynamic queries, temporary tables and updates in a loop, but isn't there a easier way to do that? Hope you experts know a way 🙂
thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

w.lengenfelder SSC Eights! Points: 884 More actions · Answer 1

Hi Matt,

this can be done, using the Pivot syntax within SQL Select.

Examples found at BOL

pawan.falor Old Hand Points: 307 More actions · Answer 2

Here is the implemented example using PIVOT..

/*Script to create Sample Data*/

DECLARE @PercentData

TABLE(Name VARCHAR(50),Department VARCHAR(10), Percentage float)

INSERT INTO @PercentData(Name,department,Percentage)

SELECT 'Gustav','GW',80 UNION ALL

SELECT 'Gustav','NW',20 UNION ALL

SELECT 'Olaf','D',100 UNION ALL

SELECT 'Detlev','NW',50 UNION ALL

SELECT 'Detlev','D',50

/*Pivot Query to get required output*/

SELECT [Name], GW, NW, D

FROM (SELECT [Name], Department, Percentage

FROM @PercentData) E

PIVOT (MAX(Percentage)

FOR department IN ([GW],[NW],[D])) X

matt-988012 SSC Journeyman Points: 87 More actions · Answer 3

Hi,

thanks for you answers! Unfortunately we are still using MSSQL2000 so I have to use PIVOT tables like this:

SELECT

mana,

SUM(CASE WHEN abna='NW' THEN pro ELSE 0 END) AS NW,

SUM(CASE WHEN abna='GW' THEN pro ELSE 0 END) AS GW,

SUM(CASE WHEN abna='D' THEN pro ELSE 0 END) AS D

FROM ma_ab_pro

INNER JOIN ma ON ma.maid = ma_ab_pro.maid

INNER JOIN ab ON ab.abid = ma_ab_pro.abid

GROUP BY mana

But "PIVOT" was the hint i needed 🙂

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Actually, your solution is more commonly referred to as a crosstab, not a pivot.

PIVOT is an SQL keyword in 2005 and up.

Why are you posting in the 2005 forum, if you are still using SQL 2000?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

matt-988012 SSC Journeyman Points: 87 More actions · Answer 5

Thanks for telling me the correct name.

We use a mixed enviroment with 7, 2000 and 2005 servers and I thought it was a 2005 server.

If it bothers you so much maybe a mod can move this topic.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

I didn't lose any sleep over it, but please remember it for the future. There are lots of new and improved techniques between 2005 and 2000. Ask a question in a 2005 forum and you'll get a 2005 answer. It wastes not only the volunteers' time but your time for them to give you an answer you can't use. Also, someone looking for an answer to a similar problem might not think to look here if they are on a 2000 server themselves. That's why there are separate forums.

Have a good weekend 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills