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

Splitting one row into multiple Expand / Collapse
Author
Message
Posted Wednesday, August 27, 2014 5:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:35 AM
Points: 3, Visits: 10
Hi!

I'm quite new to SQL. I'm able to extract the info that I need, but only into a result of one row, like:

Order header | Order details

ID | Customer name | Customer address | Product number | Product name | Quantity | Price | Product number | Product name | Quantity | Price
2 Andy Andy's way 2 24 Glue 3 35 39 Oyster 2 9

I would like the query to return it in this way:

2 Andy Andy's way 2
24 Glue 3 35
39 Oyster 2 90

Is it even possible? Thanks in advance :)
Post #1608100
Posted Thursday, August 28, 2014 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 13,446, Visits: 12,308
jonatan.carlberg (8/27/2014)
Hi!

I'm quite new to SQL. I'm able to extract the info that I need, but only into a result of one row, like:

Order header | Order details

ID | Customer name | Customer address | Product number | Product name | Quantity | Price | Product number | Product name | Quantity | Price
2 Andy Andy's way 2 24 Glue 3 35 39 Oyster 2 9

I would like the query to return it in this way:

2 Andy Andy's way 2
24 Glue 3 35
39 Oyster 2 90

Is it even possible? Thanks in advance :)


Hi and welcome to the forums. We can help but we need some more details. Does your table always have this format or is this a flat file and you need to determine how to parse it?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1608271
Posted Thursday, August 28, 2014 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
-- Prepare some sample data to code against
DROP TABLE #Sample
CREATE TABLE #Sample (
ID INT,
[Customer name] VARCHAR(20),
[Customer address] VARCHAR(20),
[Something] INT,

[Product number] INT,
[Product name] VARCHAR(20),
Quantity VARCHAR(20),
Price VARCHAR(20),

[Product number2] VARCHAR(20),
[Product name2] VARCHAR(20),
Quantity2 VARCHAR(20),
Price2 INT
)
INSERT INTO #Sample (ID,[Customer name],[Customer address],[Something],
[Product number],[Product name],Quantity,Price,[Product number2],[Product name2],Quantity2,Price2)
SELECT
2, 'Andy', 'Andy''s way', 2,
24, 'Glue', 3, 35,
39, 'Oyster', 2, 9

-- First try at a solution.
-- It's not perfect: there's some confusion about the first four columns.
-- I think there should be two product lines, each for customer 'Andy'.
-- You decide.
SELECT
x.[Product number], x.[Product name], x.Quantity, x.Price
FROM #Sample s
CROSS APPLY (VALUES
(ID, [Customer name], [Customer address], [Something]),
([Product number], [Product name], Quantity, Price),
([Product number2], [Product name2], Quantity2, Price2)
) x ([Product number], [Product name], Quantity, Price)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1608314
Posted Thursday, August 28, 2014 12:48 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:26 AM
Points: 1,966, Visits: 5,110
Here is a quick sample solution, much worse performance wise than Chris's but I put it forward more as an alternative method anyway.

USE tempdb;
GO
;WITH TDOC(
[ID]
,[Customer name]
,[Customer address]
,[Product number]
,[Product name]
,[Quantity]
,[Price]
,[Product number2]
,[Product name2]
,[Quantity2]
,[Price2]
)
AS
(
SELECT
[ID]
,[Customer name]
,[Customer address]
,[Product number]
,[Product name]
,[Quantity]
,[Price]
,[Product number2]
,[Product name2]
,[Quantity2]
,[Price2]
FROM (VALUES
(2, 'Andy' ,'Andy''s way 2',24, 'Glue',3,35,39,'Oyster',2,9)
,(3, 'John' ,'Dandy''s way 4',15,'Slime',2,10,17,'Clan' ,4,12)
) AS X([ID]
,[Customer name]
,[Customer address]
,[Product number]
,[Product name]
,[Quantity]
,[Price]
,[Product number2]
,[Product name2]
,[Quantity2]
,[Price2])
)
,CUSTOMER AS
(
SELECT
T.ID AS COL_1
,T.[Customer name] AS COL_2
,T.[Customer address] AS COL_3
,NULL AS COL_4
,NULL AS COL_5
FROM TDOC T
)
,PRODUCT_1 AS
(
SELECT
T.ID
,CONVERT(VARCHAR(25),T.[Product number],1) AS [Product number]
,T.[Product name]
,T.[Quantity]
,T.[Price]
FROM TDOC T
)
,PRODUCT_2 AS
(
SELECT
T.ID
,CONVERT(VARCHAR(25),T.[Product number2],1) AS [Product number]
,T.[Product name2]
,T.[Quantity2]
,T.[Price2]
FROM TDOC T
)
SELECT
*
FROM CUSTOMER C
UNION ALL
SELECT
*
FROM PRODUCT_1
UNION ALL
SELECT
*
FROM PRODUCT_2


Post #1608387
Posted Friday, August 29, 2014 5:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:35 AM
Points: 3, Visits: 10
Hi!

I don't quite understand what you mean. English is my second language, and even though I speak a lot of English it can be confusing when talking in terms. So pardon me for being a little "stupid" from time to time.

I will extend my question.

From the query I had written earlier the row comes out like: (Semicolons marks different columns.)

ID;Customer name;Customer address;Product number;Product name;Quantity;Price;Product number;Product name;Quantity;Price

2;Andy;Andy's way 2;24;Glue;3;35;39;Oyster;2;9
3;Tom;Tom's way 3;39;Oyster;2;9

I want it to be:

ID;Customer name;Customer address
Product number;Product name;Quantity;Price
Product number;Product name;Quantity;Price

2;Andy;Andy's way 2;
24;Glue;3;35
39;Oyster;2;9
3;Tom;Tom's way 3;
39;Oyster;2;9

So by this we can see that the first row (containing the order header) is always going to be the same, and after that there should be order item rows. In the database they are connected to each other by ID (orderadress.id_order_details and orderdetail.id_order_details) which will be the common denominator. But as far as the order item rows goes there might be 1 or a hundred of them.

How I made anything more clear, or am I just talking gibberish as usual? :P

Thanks in advance!
Post #1608553
Posted Friday, August 29, 2014 5:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:35 AM
Points: 3, Visits: 10
Hi again!

Thank you all for welcoming me to the forum. As you probably see, I've posted a clarification on my previous post.

Thanks for all input so far.

From what I can see, the to examples (i.e. solutions) they just create a temp table, so nothing will be altered in the real tables, right?

Once again thank you!
Post #1608554
Posted Friday, August 29, 2014 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
jonatan.carlberg (8/29/2014)
Hi again!

Thank you all for welcoming me to the forum. As you probably see, I've posted a clarification on my previous post.

Thanks for all input so far.

From what I can see, the to examples (i.e. solutions) they just create a temp table, so nothing will be altered in the real tables, right?

Once again thank you!


Hi Jonatan

Can you please set up the sample data in the same way as I did in an earlier post in your thread, using CREATE TABLE/INSERT? This provides us all with a number of great advantages - which is all good news for you. Many thanks.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1608557
Posted Monday, September 1, 2014 7:12 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:29 PM
Points: 3,648, Visits: 5,322
Easy enough using Jeff Moden's famous DelimitedSplit8K function:

WITH SampleData (s) AS
(
SELECT '2;Andy;Andy''s way 2;24;Glue;3;35;39;Oyster;2;9'
UNION ALL SELECT '3;Tom;Tom''s way 3;39;Oyster;2;9'
),
SplitList AS
(
SELECT Item, ItemNumber=CASE WHEN ItemNumber <= 3 THEN 0 ELSE ItemNumber/4 END, s
FROM SampleData
CROSS APPLY dbo.DelimitedSplit8K(s, ';')
)
SELECT s2=STUFF(
(
SELECT ';' + Item
FROM SplitList a
WHERE a.s = b.s AND a.ItemNumber = b.ItemNumber
FOR XML PATH('')
), 1, 1, '')
FROM SplitList b
GROUP BY s, ItemNumber;


Find that function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Tally Oh!



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 #1609422
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse