simple transpose ? pivot ? - 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 2008 Forums

Transact-SQL (2008)

simple transpose ? pivot ?

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-08-04 : 11:36:11

i just got a table tblarticles with all the stuff that goes along with it... like dimensions,price ....

in my program i always query my table returning only 1 result, that of the article i want ....

now i want to show all the columns i want, but pivotted so i get this:

header1 header 2
price 10
dimensions 10x10x10

you get the idea.... very simple actually , the examples i get on the web are rather complex :p my result only has two columns ...

thx alot in advance

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-08-06 : 03:53:46

help :(

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 05:06:48

Dont know if this is what you want?


DECLARE  @tbl  AS  TABLE( 
                         TYPE  VARCHAR(40), 
                         VALUE INT 
                         ) 

INSERT INTO @tbl 
SELECT 'price', 
       10 
UNION ALL 
SELECT 'dimensions', 
       10 
UNION ALL 
SELECT 'dimensions', 
       10 
UNION ALL 
SELECT 'dimensions', 
       10 

SELECT * 
FROM   @tbl 

SELECT * 
FROM   (SELECT   TYPE AS header1, 
                 Stuff((SELECT 'x' + Convert(VARCHAR(20),VALUE) 
                        FROM   @tbl t1 
                        WHERE  t1.TYPE = t2.TYPE 
                        for 
                              XML path('') 
                       ),1,1,'') AS header2 
        FROM     @tbl t2 
        GROUP BY TYPE) t

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-08-06 : 05:22:28

sorry, ... but i dont think so....

i get this result

articlename price dimensionx
banana 10 100

is just want to pivot this ( i always get only 1 row [so TOP 1] , to keep it simple )

i want this transformed into

articlename banana
price 10
dimensionx 100

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 05:25:39

quote:
Originally posted by doubleotwo

sorry, ... but i dont think so....

Then post some sample data & expected o/p.
It makes others keep guessing.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-08-06 : 07:26:26

yeah idera , sorry but i did that in my last post ...

its just that i get this when i query my table

SELECT * FROM tblarticle WHERE articleid = 1

cols id price bla1 bla 2
values 1 100 5 6

i want this to be transformed into folowing output

id 1
price 100
bla....

you may expect that i make my query always with and WHERE ID = ... so there"s only 1 row max

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 07:37:46

[code]
declare @tbl as table(id int,price int,bla1 int,bla2 int,bla3 int)
insert into @tbl
select 1, 100, 5, 6,7

select columns,col from
(
select * from @tbl
)u
unpivot
(col for columns in(id,price,bla1,bla2,bla3))v

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources