PIVOT confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT confusion

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-01-23 : 11:20:42

I have in a row of data:

Item1 Item2 Item3
Box Car Boot

I would like to have this returned as:

Box
Car
Boot

Is there a way to do this and if so can it be stored in a table on SQL and be linked to a front-end for reporting?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:25:55

quote:
Originally posted by osupratt

I have in a row of data:

Item1 Item2 Item3
Box Car Boot

I would like to have this returned as:

Box
Car
Boot

Is there a way to do this and if so can it be stored in a table on SQL and be linked to a front-end for reporting?

If it is a single row this is enough

select Item1 as Item from yourtable union all
select Item2 from yourtable union all
select Item3 from yourtable

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:30:32

search for unpivot in this forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:34:02

or simply use


SELECT Item
FROM
(
SELECT Item1 AS Item,1 AS Cat FROM Table
UNION ALL
SELECT Item2,2 FROM Table
UNION ALL
SELECT Item3,3 FROM Table
)t
ORDER BY Cat

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:36:03

for UNPIVOT it will be


SELECT *
FROM(SELECT * FROM Table)t
UNPIVOT(Item FOR Val IN ([Item1],[Item2],[Item3]))p

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:37:51

try this

declare @Temp table
(
item1 varchar(64),
item2 varchar(64),
item3 varchar(64)
)

insert into @temp
select 'Box','Car','Boot' union all
select 'baloon','bomb','gun'

select item
from
(select * from @temp) t
unpivot(item for itemvalue in (item1,item2,item3)) i

osupratt
Posting Yak Master

238 Posts

Posted - 2009-01-23 : 11:40:06

thanks for all your help. that makes perfect sense. duh. anyhow, could i build a table in sql and then have this info inserted into it? i have end-users on an Access front-end that need a ad-hoc report off the Access switchboard. thanks for the responses.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-23 : 11:43:05

Welcome...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:44:21

quote:
Originally posted by osupratt

thanks for all your help. that makes perfect sense. duh. anyhow, could i build a table in sql and then have this info inserted into it? i have end-users on an Access front-end that need a ad-hoc report off the Access switchboard. thanks for the responses.

you can . just use


SELECT Item INTO YourTable
FROM(SELECT * FROM Table)t
UNPIVOT(Item FOR Val IN ([Item1],[Item2],[Item3]))p

osupratt
Posting Yak Master

238 Posts

Posted - 2009-01-23 : 11:50:37

Not being a pro at this...my question is....

when the front end (access) is linked to the back end (sql 2005) is the table that i create and the code that 'unpivots'; does this happen automatically or just when executed?

simply put, how does this new table stay updated? thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:54:04

nope. it needs to be executed. wont happen automatically.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-23 : 23:46:39

quote:
Originally posted by osupratt

Not being a pro at this...my question is....

when the front end (access) is linked to the back end (sql 2005) is the table that i create and the code that 'unpivots'; does this happen automatically or just when executed?

simply put, how does this new table stay updated? thanks again!

u can write that query in the storedprocedure and call the sp in ur front end then it will be easy i think so to show the output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources