Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting particular set of Rows as Columns Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 9:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:15 AM
Points: 177, Visits: 77
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
Post #1448672
Posted Wednesday, May 1, 2013 9:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
Can you please provide a detailed example with table scripts, sample data, and the expected results like this? Also, can you please describe how are the sets of rows determined (e.g. is it always going to be "Mango and Orange"?)
Post #1448673
Posted Wednesday, May 1, 2013 10:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,224, Visits: 31,683
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1448675
Posted Wednesday, May 1, 2013 10:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,224, Visits: 31,683
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1448677
Posted Thursday, May 2, 2013 2:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:15 AM
Points: 177, Visits: 77
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.
Post #1448721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse