changing style of sql table - 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)

changing style of sql table

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-03 : 10:54:46

Hi,

Ive got a table i put together through a SP and I want to put it into a matrix in SSRS. But noticed that the matrix in SSRS only takes one column value on the X and Y axis so I need to put all the values I need into 2 columns.

For example:

Table1:
Product, Unit, Price, NetRev
Xbox, 44, '33,000','3,450,000'

I would like:

Table1:
Product, InfoType, Value
Xbox, Unit, 44
Xbox, Price, '33,000'
Xbox, NetRev, '3,450,000'

Any ideas?

Much appreciated.

Cipriani

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-03 : 12:01:31

Help?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-03 : 12:40:56

You could probably use a PIVOT or UNPIVOT. But, a simple way is just to use UNION, but how do you want to handle the different data types?:

SELECT Product, 'Unit' AS InfoType, CAST(Unit AS VARCHAR(4000)) AS Value
UNION ALL
SELECT Product, 'Price', CAST(Price AS VARCHAR(4000))
UNION ALL
SELECT Product, 'NetRev', CAST(Price AS VARCHAR(4000))

In my sample I converted all the Values to VARCHAR, which is a terrible idea if you are storing this data in a database.

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-04 : 04:43:28

No I just wanted to temporarily do this in a report so I can use it in a matrix, ill give it a try in a minute. Thanks for the prompt replies but if any other solutions out there would be greatful.

Cipriani

quote:
Originally posted by Lamprey

You could probably use a PIVOT or UNPIVOT. But, a simple way is just to use UNION, but how do you want to handle the different data types?:
SELECT Product, 'Unit' AS InfoType, CAST(Unit AS VARCHAR(4000)) AS Value
UNION ALL
SELECT Product, 'Price', CAST(Price AS VARCHAR(4000))
UNION ALL
SELECT Product, 'NetRev', CAST(Price AS VARCHAR(4000))
In my sample I converted all the Values to VARCHAR, which is a terrible idea if you are storing this data in a database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources