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

How to Build a single row with groups of fields from several rows per key Expand / Collapse
Author
Message
Posted Saturday, October 6, 2012 11:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 1:39 PM
Points: 11, Visits: 16
i have several rows like:
Customer sn val1, val2, Val3 ..... (sn is a seq# from 1 to 15)
A 1 31 54
A 2 98 21 65
A 3 44 67 11
B ...
I want to get A sinle row:
Customer val1_1, Val1_2, val1_3, val2_1, val2_2, Val2_3, val3_1, val3_2,.....
A 31 54 98 21 65 44 67
B

The Single row built will be used to update a database table.

Please an efficient solution.



Post #1369453
Posted Saturday, October 6, 2012 1:25 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
Avraham de-Haan (10/6/2012)
i have several rows like:
Customer sn val1, val2, Val3 ..... (sn is a seq# from 1 to 15)
A 1 31 54
A 2 98 21 65
A 3 44 67 11
B ...
I want to get A sinle row:
Customer val1_1, Val1_2, val1_3, val2_1, val2_2, Val2_3, val3_1, val3_2,.....
A 31 54 98 21 65 44 67
B

The Single row built will be used to update a database table.

Please an efficient solution.


Of course, "It Depends", but putting it all on one row is likely not the most efficient solution to begin with. We might be able to come up with something very effecient if you explained what these values actually represent.


--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 #1369457
Posted Saturday, October 6, 2012 4:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 1:39 PM
Points: 11, Visits: 16
It is just an example, my data is more complicated.
Actually I have from 0 to 15 data/trailer rows (like orders) for each customer.
Now I need to send the customer data _including_ all it's order/trailers in 1 single (long) row.
My example imitates these "order" rows which I need to combine into up to 15 "groups" of fields in the output row and glue them to each customer data.
I hope I clarified myself.
Thanks



Post #1369478
Posted Sunday, October 7, 2012 7:19 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
Avraham de-Haan (10/6/2012)
It is just an example, my data is more complicated.
Actually I have from 0 to 15 data/trailer rows (like orders) for each customer.
Now I need to send the customer data _including_ all it's order/trailers in 1 single (long) row.
My example imitates these "order" rows which I need to combine into up to 15 "groups" of fields in the output row and glue them to each customer data.
I hope I clarified myself.
Thanks


Yep.... knew it was just example data and that's why I asked for a bit more detail.

So, each group will have 3 entries (or less as in your example) and each customer can have up to 15 groups. Got that.

0. I'm assuming that the data-type in the original table for the 3 entries for each group is INT. Is that correct?

1. What do you want to return for a customer with 0 groups and how will that be manifested in the original table.

2. Also, since you're sending this to a customer and it sounds a whole lot like they're going to be importing the data either into a spreadsheet or a table, what do you want for delimiters between the "columns" of data in the output? Commas? Tabs? or ...

3. ... or do you want this to be a fixed field format? If so, what are the data-types in the 3 values of each group in the original table? Need to know this to be able to handle the max width.

Also, since you're kind of new on this forum, please take a look at the article at the first link in my signature line below. Posting readily consumable data (even if it's just example data) like that would make it so we didn't have to ask so many questions to help you. Thanks.


--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 #1369526
Posted Monday, October 8, 2012 11:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 1:39 PM
Points: 11, Visits: 16
Thanks for your questions I had to think about it in advance in my explanation.
0. I'm assuming that the data-type in the original table for the 3 entries for each group is INT. Is that correct?
Mixed in and Nvarchar fields

1. What do you want to return for a customer with 0 groups and how will that be manifested in the original table.
Nullified fields

2. Also, since you're sending this to a customer and it sounds a whole lot like they're going to be importing the data either into a spreadsheet or a table, what do you want for delimiters between the "columns" of data in the output? Commas? Tabs? or ...
I am sending it from a WebService so XML data

3. ... or do you want this to be a fixed field format? If so, what are the data-types in the 3 values of each group in the original table? Need to know this to be able to handle the max width.
It is fixed format so it has 9 fields per customer/group of fields. A customer with no optional trailer rows (Orders?) all its fields will (stay) nullified.
The number of Customers handled is of small volume (between 1 to 10?) each with 0 to 15 batches/groups of 9 fields.

I hope I am now clear enough,
Thanks



Post #1369963
Posted Tuesday, October 9, 2012 7:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 3,609, Visits: 5,219
Something like this perhaps?

DECLARE @T TABLE
(Customer VARCHAR(5), sn INT, val1 INT, val2 INT, Val3 INT)

INSERT INTO @T
SELECT 'A', 1, 31, 54, NULL
UNION ALL SELECT 'A', 2, 98, 21, 65
UNION ALL SELECT 'A', 3, 44, 67, 11

;WITH MyVals AS (
SELECT Customer, sn, n=1, val=val1
FROM @T
UNION ALL
SELECT Customer, sn, 2, val2
FROM @T
UNION ALL
SELECT Customer, sn, 3, val3
FROM @T
)
SELECT Customer
,val1_1=MAX(CASE WHEN sn=1 AND n=1 THEN val END)
,val1_2=MAX(CASE WHEN sn=1 AND n=2 THEN val END)
,val1_3=MAX(CASE WHEN sn=1 AND n=3 THEN val END)
,val2_1=MAX(CASE WHEN sn=2 AND n=1 THEN val END)
,val2_2=MAX(CASE WHEN sn=2 AND n=2 THEN val END)
,val2_2=MAX(CASE WHEN sn=2 AND n=3 THEN val END)
,val3_1=MAX(CASE WHEN sn=3 AND n=1 THEN val END)
,val3_2=MAX(CASE WHEN sn=3 AND n=2 THEN val END)
,val3_3=MAX(CASE WHEN sn=3 AND n=3 THEN val END)
FROM MyVals
WHERE val IS NOT NULL
GROUP BY Customer





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1370648
Posted Wednesday, October 10, 2012 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 1:39 PM
Points: 11, Visits: 16
As I have up to 15 "groups" of 9 fields each - this is a long way to go.
Only a few customers in each session.



Post #1371028
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse