Grouping Multiple Lines to One

Question

Grouping Multiple Lines to One

nicolestrain07

SSC Journeyman

Points: 84
More actions
October 11, 2017 at 2:43 am

#339815

Hello,
Im trying to combine multiple lines to one like the below example.
I looked at piviot but as im not doing any calculations i didnt think that would be the most relevant. I also looked at group_concat but i can only find solutions for multiple columns in MySQL and not SQL.
The idea is to group multiple orders into one line and the max orders anyone can place is 4.
Does anyone have any suggestions ?
Thank you 🙂

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

What about if a name has Order A, B And C. Are you expecting 3 columns, or are the only options A and B? Could it go all the way of to Z? Maybe more (ZZZ?)?

Generally this sort of query is frowned upon at SQL level. If you need to pivot data, you should do it in your presentation layer.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sgmunson SSC Guru Points: 110639 More actions · Answer 2

Add a ROW_NUMBER() function that partitions by Name and Address, and then ORDER BY [Order], and then you can use CASE statements for a sort of CROSS TAB query that uses GROUP BY Name, Address and does a MAX(CASE WHEN RowNum = 1 THEN [Order] ELSE NULL END) AS Order1, and similar lines for the other 3 possible orders. I assume here that you use a CTE to derive the row numbers and call that column RowNum.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

nicolestrain07 - Wednesday, October 11, 2017 2:43 AM
Hello,
Im trying to combine multiple lines to one like the below example.
I looked at piviot but as im not doing any calculations i didnt think that would be the most relevant. I also looked at group_concat but i can only find solutions for multiple columns in MySQL and not SQL.
The idea is to group multiple orders into one line and the max orders anyone can place is 4.
Does anyone have any suggestions ?
Thank you 🙂

Please see the following article. Use MAX instead of SUM.
Cross Tabs and Pivots, Part 1 â€“ Converting Rows to Columns

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