Return row values as columns

Question

Return row values as columns

goodyes

Right there with Babe

Points: 785
More actions
April 11, 2008 at 7:38 am

#379718

Hello.
I need to know -with sample code if possible- how to put row(values) as columns in a select query or similar.
I'm go to show following example that i need to resolve
-WHAT ARE THE TABLES AND THOSE ROWS-
TABLE 1 - "ITEMS"
-------------------------------------------------------------------------------
ID_Item(PK)ItemName
1Bicycle-Ultra
2Barbie And Ken
3MotorBike
....
TABLE 2 - "CustomFieldsValues"
-------------------------------------------------------------------------------
ID_Item(FK)ID_CFvalue(PK)ID_CFName(FK)CFValue(nVarChar)
111Carbon & Titan
122Michelin
233Minimum 4 years
...... ..
TABLE 3 - "CustomFieldsNames"
-------------------------------------------------------------------------------
ID_CFName(PK)CFName(nVarChar)
1MaterialOfFrame
2Tires
3Age Required
....
MY ACTUAL TABLE RESULT ::: USING SIMPLE SELECT WITH JOIN's QUERY :::
[font="Courier New"]TABLE 1 - "ITEMS"TABLE 2 - "CustomFieldsValues"TABLE 3 - "CustomFieldsNames"
-------------------------------------------------------------------------------------------------------------------
ID_Item(PK)ItemName ID_CFvalue(PK)CFValue(nVarC)ID_CFName CFName(nVarC)
1Bicycle-Ultra1Carbon & Titan 1Material
1Bicycle-Ultra2Michelin 2Tires
2Barbie AndKen3Plastic 3Material
3MotorBike 4Bridgestone2Tires
3Motorbike 6DoubleFocus4Lights
3MotorBike 5NULL 2Material
[/font]
:::WHAT ARE THE RESULT TABLE I WANT TO GET::: with a query or SP
With the current schema that i've expose What t-SQL2005 Code i need to get this results
-------------------------------------------------------------------------------------------------------------------
ID_Item(PK)ItemNameMaterial(ones that have ID_CFName=1)Tires(ID_CFName=2)
1Bicycle-Ultra Carbon & Titan Michelin
2Barbie AndKenPlastic NULL
2MotorBike Aluminum Bridgestone
WHAT CODE IS NEEDED IF -AS MY WISH- I WANT TO RETRIEVE Items with the ROWS VALUES of table "CustomFieldsValues" AS COLUMNS; Material and Tires (ID_CFName=1 and 2)
Thanks a much lot
Probably not as difficult as it does for me
Any comment will be much apreciated

Viewing 5 posts - 1 through 4 (of 4 total)

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

blast2hell Right there with Babe Points: 771 More actions · Answer 1

I'm sure someone smarter then me may come along with a good recommendation, but I think what your asking for is to pivot the table. Check this article out and see if it helps any

PIVOT clause

goodyes Right there with Babe Points: 785 More actions · Answer 2

Thanks, I will check on nexts hours ..

But as far as i search last 4 hours some related on inet, the PIVOT / UNPIVOT related sources was seems difficult to understand by myself.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Read the article at the URL in my signature line... you'll get a lot better help...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

goodyes Right there with Babe Points: 785 More actions · Answer 4

Thanks to all.

Finally i found the solutions, on a thirds urls that i found.

Thanks to GrassHopper and the other one. The GrassH Url's serves me a trick to found more info and other urls, on witch i can make the solution work.

The technique used is PIVOT but without Aggregate !!

It's a hurt that nobody's post code, but as i mentioned above, finally i accomplish this task (1/*n jj)

Urls:

http://www.mssqltips.com/tip.asp?tip=1019

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20282

http://msdn2.microsoft.com/en-us/library/ms177410.aspx

I will make a post in my blog:

http://abmartin.wordpress.com <-- here i write about .NET and SQL