Converting particular set of Rows as Columns

Question

Converting particular set of Rows as Columns

GGadde

SSC Veteran

Points: 269
More actions
May 1, 2013 at 9:34 pm

#293412

Converting one row as one Column is possible using Pivot. But is there any way to convert particular set of rows to one column?
For example, below is the data table
Customer---------------Product-------------Qty
Jain --------------- Mango -------------10
Charlie --------------- Orange--------------5
Rocky --------------- Mango---------------6
Rocky ---------------- Orange---------------3
Expected Result set
Customer---------------Mango---------------Orange---------------Mango and Orange
Jain---------------------10-------------------NULL ---------------------NULL
Charlie------------------NULL-------------------5-----------------------NULL
Rocky-------------------NULL-----------------NULL---------------------- 9
Appreciate any help
* ---- are used for identation purpose only
Thank You 🙂

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

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

Mansfield SSCrazy Points: 2396 More actions · Answer 1

Can you please provide a detailed example with table scripts, sample data, and the expected results like this[/url]? Also, can you please describe how are the sets of rows determined (e.g. is it always going to be "Mango and Orange"?)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

gadde (5/1/2013)
Converting one row as one Column is possible using Pivot. But is there any way to convert particular set of rows to one column?
For example, below is the data table
Customer---------------Product-------------Qty
Jain --------------- Mango -------------10
Charlie --------------- Orange--------------5
Rocky --------------- Mango---------------6
Rocky ---------------- Orange---------------3
Expected Result set
Customer---------------Mango---------------Orange---------------Mango and Orange
Jain---------------------10-------------------NULL ---------------------NULL
Charlie------------------NULL-------------------5-----------------------NULL
Rocky-------------------NULL-----------------NULL---------------------- 9
Appreciate any help
* ---- are used for identation purpose only
Thank You 🙂

What you have there is actually a Pivot or, in old terms, a Cross Tab. Here's a couple of articles that explain (step by step in simple terms) how to hardcode one and how to do it dynamically on the other.

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

--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)

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

Ah... sorry. Might not be right for you. Didn't see where you doubled up on "Rocky". the dynamic SQL article I previously mentioned might help, though.

--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)

GGadde SSC Veteran Points: 269 More actions · Answer 4

Hi All,

Thanks for the help.

Got it 🙂

Below is the query:

select * from

(select customer

,sum(qty) qty1

,(select cast(product as varchar(max)) from datatable where customer = a1.customer for XML path(''))product

from datatable a1

group by customer

)dataforpivot_table

pivot (sum(qty1) for product in ([Mango],[Orange],[MangoOrange]))pivoted;

Products are not fixed, they keep on changing, and even the combinations change.