August 27, 2014 at 5:10 pm
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:
2AndyAndy's way 2
24Glue335
39Oyster290
Is it even possible? Thanks in advance 🙂
August 28, 2014 at 7:21 am
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:
2AndyAndy's way 2
24Glue335
39Oyster290
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2014 at 8:48 am
-- 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)
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
August 28, 2014 at 12:48 pm
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
August 29, 2014 at 5:53 am
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? 😛
Thanks in advance!
August 29, 2014 at 5:56 am
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!
August 29, 2014 at 5:59 am
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.
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
September 1, 2014 at 7:12 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply