(HopeFully) Easy T-SQL issue with PIVOT or FOR XML

Question

Post reply

(HopeFully) Easy T-SQL issue with PIVOT or FOR XML

abrahamb

SSC Veteran

Points: 248
More actions
January 15, 2008 at 10:48 am

#202351

I need to get output from a standard query like SELECT * FROM TABLE to come out like the followng:
Name Value
ColName1 ColValue
Colname2 ColValue
.....
I tried using PIVOT and FOR XML with an XQuery but I must be missing something - there has got to be an easy answer to this that escapes me....
Help!
Thanks!

Viewing 10 posts - 1 through 9 (of 9 total)

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

abrahamb SSC Veteran Points: 248 More actions · Answer 1

Guess this was tougher than I thought - no posts....

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Abraham -

I'm not quite sure I get specifically what you're looking to do. You might be able to do this with a PIVOT (or the old-style version of that).

could you mock up what the original data looks like and what you'd specifically want it to look like. Doesn't need to be much - just enough to be illustrative.

What challenges did you run into with PIVOT the first time?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 3

you can write the query like this

Select 'Col1name' as [ColumnName], col1 as [Value] from table

union all

Select 'Col2name' as [ColumnName], col2 as [Value] from table

union all

Select 'Col3name' as [ColumnName], col3 as [Value] from table

abrahamb SSC Veteran Points: 248 More actions · Answer 4

Yup..data looks like this

- standard table format

col1 |col2 |col3

Row1 val1 |val2 |val3

Row2 val1 |val2 |val3

Row3 val1 |val2 |val3

I need it displayed as

For Row1

Col1name Val1

Col2name Val2

Col3name Val3

For Row2

Col1name Val1

Col2name Val2

Col3name Val3

abrahamb SSC Veteran Points: 248 More actions · Answer 5

I'll try dynamically generating the SQL in a proc with the select commands that were proposed - Thanks!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Ah - the visual... No wonder you were having trouble: you want to UNPIVOT, not PIVOT...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

abrahamb SSC Veteran Points: 248 More actions · Answer 7

Cool - I'll try UNPIVOT - do you have a very simple example of this?

select * FROM table

UNPIVOT xxxx...

I tried getting PIVOT to work and never could get the syntax right....

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Try UNPIVOT.

Pivot is for turning values into column headers, unpivot is for turning column names into values.

Edit: Beaten to it. 😀

There are examples in books online. The main thing to watch for is the position of brackets. Catches me with pivot every time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

coming right up!

create table ToUnpivot (myGroupname varchar(20), val1 int, val2 int, val3 int)

insert ToUnpivot

select 'aaa',1,2,3 UNION ALL

select 'bbb',10,12,13 UNION ALL

select 'ccc',21,22,23 UNION ALL

select 'ddd',1,2,3 UNION ALL

select 'eee',11,12,13 UNION ALL

select 'fff',41,42,43

go

--now - for the unpivot

Select mygroupname, MyCategory,MyVal

from (select mygroupName,

Val1,

val2,

val3 from ToUnpivot) as TU

UNPIVOT (MyVal --first alias mentioned in unpivot = contents of the field

for MyCategory --second alias = the field names

in ([Val1],[Val2],[Val3]) --the actual columns to unpivot

) as UNpvt

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?