displaying horizontal rows in vertical format

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

Development Tools

Reporting Services Development

displaying horizontal rows in vertical format

Author

Topic

smc123
Starting Member

1 Post

Posted - 2010-10-11 : 17:56:46

The results from my query are displayed in rows in which each row corresponds to an entity. Each entity has about 12 data items. I am trying to get the data to be displayed in a vertical format.

Ex: For results,
name1 type1 color1 qty1 note1
name2 type2 color2 qty2 note2

I would like to see:
name1
type1
color1
qty1
note1

name2
type2
color2
qty2
note2

I am using SQL2008 and Microsoft SQL Server Report Builder. I am happy to switch to any report builder that can do this. In Microsoft SQL Report Builder I have used the table, matrix, and list options but cannot get the data to display the way I'd like to see it.

Any ideas you can share are much appreciated.

bobzor
Starting Member

10 Posts

Posted - 2010-10-12 : 16:09:08

Start with a List, so it will repeat once per row in your dataset. Add textboxes inside the list, and assign fields to them.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-12 : 17:46:28

For a t-sql solution you can use UNPIVOT to transform columns to rows:


declare @t table (id int, nm varchar(10), typ varchar(10), color varchar(10), qty int, txt varchar(10))
insert @t
select 1, 'name1', 'type1', 'color1', 10, 'note1' union all
select 2, 'name2', 'type2', 'color2', 20, 'note2' 

select up.id
       ,up.col as sourcecolumn
       ,up.val as [value]
from   (
       select id, nm,typ,color,convert(varchar(10), qty) qty,txt
       from @t --YOUR TABLE
       ) d
unpivot (val for col in ([nm], [typ], [color],[qty],[txt])) up

OUTPUT:
id          sourceColumn  value
----------- ----------    ----------
1           nm            ame1
1           typ           type1
1           color         color1
1           qty           10
1           txt           note1
2           nm            name2
2           typ           type2
2           color         color2
2           qty           20
2           txt           note2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources