Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Converting particular set of Rows as Columns Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, May 01, 2013 9:34 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, October 23, 2013 2:24 AM Points: 160, Visits: 64
 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 tableCustomer---------------Product-------------QtyJain --------------- Mango -------------10Charlie --------------- Orange--------------5Rocky --------------- Mango---------------6Rocky ---------------- Orange---------------3Expected Result setCustomer---------------Mango---------------Orange---------------Mango and OrangeJain---------------------10-------------------NULL ---------------------NULL Charlie------------------NULL-------------------5-----------------------NULLRocky -------------------NULL-----------------NULL---------------------- 9 Appreciate any help* ---- are used for identation purpose onlyThank You
Post #1448672
 Posted Wednesday, May 01, 2013 9:56 PM
 Old Hand Group: General Forum Members Last Login: Yesterday @ 7:58 PM Points: 356, Visits: 374
 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 01, 2013 10:19 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 9:41 AM Points: 34,554, Visits: 28,731
 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 tableCustomer---------------Product-------------QtyJain --------------- Mango -------------10Charlie --------------- Orange--------------5Rocky --------------- Mango---------------6Rocky ---------------- Orange---------------3Expected Result setCustomer---------------Mango---------------Orange---------------Mango and OrangeJain---------------------10-------------------NULL ---------------------NULL Charlie------------------NULL-------------------5-----------------------NULLRocky -------------------NULL-----------------NULL---------------------- 9 Appreciate any help* ---- are used for identation purpose onlyThank 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1448675
 Posted Wednesday, May 01, 2013 10:23 PM
 SSC-Dedicated Group: General Forum Members Last Login: Today @ 9:41 AM Points: 34,554, Visits: 28,731
 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." -- 04 August 2013(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #1448677
 Posted Thursday, May 02, 2013 2:02 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, October 23, 2013 2:24 AM Points: 160, Visits: 64
 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(''))productfrom datatable a1group by customer)dataforpivot_tablepivot (sum(qty1) for product in ([Mango],[Orange],[MangoOrange]))pivoted;Products are not fixed, they keep on changing, and even the combinations change.
Post #1448721

 Permissions