SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Splitting one row into multiple


Splitting one row into multiple

Author
Message
jonatan.carlberg
jonatan.carlberg
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 28
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 Smile
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25867 Visits: 17509
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 Smile


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 Modens 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)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16047 Visits: 19528
-- 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
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14900 Visits: 18591
Here is a quick sample solution, much worse performance wise than Chris's but I put it forward more as an alternative method anyway.
Cool
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



jonatan.carlberg
jonatan.carlberg
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 28
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? Tongue

Thanks in advance!
jonatan.carlberg
jonatan.carlberg
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 28
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!
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16047 Visits: 19528
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
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7209 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search