December 19, 2013 at 12:50 pm
Below is my database table
Product Id IsPrimary Quantity
P001 1
P001.1 0 2
P001.2 0 4
P001.3 0 5
P002 1
P002.1 0 6
P002.2 0 7
P002.3 0 9
P002.4 0 10
P002.5 0 11
Need the query for result each group shows multiplied value of group quantity and last row of the group is shown with NULL
Product Id SubProduct Quantity
P001 40
P001 P001.3 NULL
P002 41580
P002 P002.5 NULL
December 19, 2013 at 1:05 pm
Help us help you! Remember we're volunteers.
If you're looking for code assistance, we ask you present the data in a way that we can easily turn it into a #table on our systems to be able to test the code before we offer it as a solution. check out the first link in my signature if you need help with that or more information on what will help you get a faster solution.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 19, 2013 at 1:20 pm
Sorry for the inconvenience.
Since my table did not got pasted properly, i have attached screen shot in below link
please help
December 19, 2013 at 1:29 pm
Shanmuga Raj (12/19/2013)
Since my table did not got pasted properly, i have attached screen shot in below link
please help
I'm afraid I wasn't clear enough and you apparently didn't get a chance to check out the article in that link.
We need something that includes CREATE TABLE #something, INSERT INTO #something... etc. We need code to CREATE a table, not something we stare at and pray code works against.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 19, 2013 at 2:14 pm
CREATE TABLE [dbo].[Product_Table](
[Product Id] [char](10) NULL,
[IsPrimary] [int] NULL,
[Quantity] [numeric](18, 0) NULL
) ON [PRIMARY]
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001', 1, 0)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.1', 0, 2)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.2', 0, 4)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.3', 0, 5)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002', 1, 0)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.1', 0,6)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.2', 0,7)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.3', 0,9)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.4', 0,10)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.5', 0,11)
Need the result to be as
Product Id | SubProduct |Quantity
P001 | | 40
P001 | P001.3 |0
P002 | | 41580
P002 | P002.5| 0
December 19, 2013 at 2:25 pm
Shanmuga Raj (12/19/2013)
CREATE TABLE [dbo].[Product_Table]([Product Id] [char](10) NULL,
[IsPrimary] [int] NULL,
[Quantity] [numeric](18, 0) NULL
) ON [PRIMARY]
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001', 1, 0)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.1', 0, 2)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.2', 0, 4)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P001.3', 0, 5)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002', 1, 0)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.1', 0,6)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.2', 0,7)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.3', 0,9)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.4', 0,10)
INSERT INTO Product_Table ([Product Id], IsPrimary, Quantity) VALUES ('P002.5', 0,11)
Need the result to be as
Product Id | SubProduct |Quantity
P001 | | 40
P001 | P001.3 |0
P002 | | 41580
P002 | P002.5| 0
How do you know which ProductId you want displayed as SubProduct? And whatever you have as Quantity in the output makes absolutely no sense at all. What are those values and what is the logic for how to calculate them?
_______________________________________________________________
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/
December 19, 2013 at 7:00 pm
There are only 3 ways I can think of in SQL to get a running product. A recursive CTE, a CURSOR (gag me! :-P) and the Quirky Update (QU). Since the latter is probably faster, I recommend you try something like this:
SELECT *, TotalQty=0
,Prod=LEFT([Product Id], CHARINDEX('.', [Product Id]+'.')-1)
,ID=CAST(ISNULL(NULLIF(STUFF([Product Id], 1, CHARINDEX('.', [Product Id]+'.'), ''), ''), 0) AS INT)
INTO #Product_Table2
FROM Product_Table;
ALTER TABLE #Product_Table2 ALTER COLUMN Prod CHAR(10) NOT NULL;
ALTER TABLE #Product_Table2 ALTER COLUMN ID INT NOT NULL;
ALTER TABLE #Product_Table2 ADD CONSTRAINT pk_pt2 PRIMARY KEY (Prod, ID);
DECLARE @TotalQty INT = 1;
-- Quirky Update (QU)
UPDATE #Product_Table2 WITH(TABLOCKX)
SET @TotalQty = TotalQty = CASE ID WHEN 0 THEN 1 ELSE @TotalQty * Quantity END
OPTION (MAXDOP 1);
--SELECT * FROM #Product_Table2
SELECT c.[Product Id], c.SubProduct, c.Quantity
FROM #Product_Table2 a
CROSS APPLY
(
SELECT TOP 1 SubProduct=b.[Product Id], b.TotalQty
FROM #Product_Table2 b
WHERE IsPrimary = 0 AND a.PRod = b.Prod
ORDER BY ID DESC
) b
CROSS APPLY
(
VALUES(a.[Product Id], SubProduct, NULL), (a.[Product Id], NULL, b.TotalQty)
) c ([Product Id], SubProduct, Quantity)
WHERE IsPrimary = 1
ORDER BY c.[Product Id], c.SubProduct
GO
DROP TABLE #Product_Table2;
Of course, before you ever use a QU, you should familiarize yourself with the rules of the game. The seminal article on this is by SQL MVP Jeff Moden.
Solving the Running Total and Ordinal Rank Problems[/url]
The second CROSS APPLY in the final query is just the CROSS APPLY VALUES approach to UNPIVOT, explained in the first article in my signature links.
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
December 19, 2013 at 9:55 pm
Note that if you want Quantity on the line with the SubProduct to be zero, just change this line:
VALUES(a.[Product Id], SubProduct, NULL), (a.[Product Id], NULL, b.TotalQty)
To this:
VALUES(a.[Product Id], SubProduct, 0), (a.[Product Id], NULL, b.TotalQty)
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
December 20, 2013 at 4:27 am
dwain.c (12/19/2013)
Note that if you want Quantity on the line with the SubProduct to be zero, just change this line:
VALUES(a.[Product Id], SubProduct, NULL), (a.[Product Id], NULL, b.TotalQty)
To this:
VALUES(a.[Product Id], SubProduct, 0), (a.[Product Id], NULL, b.TotalQty)
Thanks for your detailed response and i am working now with ur code. thanks
December 21, 2013 at 3:23 am
A Simple solution:
select A.[Product Id],
(
select exp(sum(log(quantity))) from product_table B
where isprimary = 0
and charindex(ltrim(rtrim(A.[Product Id]))+'.',B.[Product Id]) > 0
) as Value
from product_table A
where
A.isPrimary = 1
Union
select A.[Product Id],Null as Value
from product_table A
where A.isPrimary = 0
and not exists
(
Select *
from product_table B
where
substring(ltrim(rtrim(B.[Product Id])), charindex('.',ltrim(rtrim(B.[Product Id]))) + 1, len(ltrim(rtrim(B.[Product Id])))) >
substring(ltrim(rtrim(A.[Product Id])), charindex('.',ltrim(rtrim(A.[Product Id]))) + 1, len(ltrim(rtrim(A.[Product Id]))))
and B.isPrimary = 0
and substring(ltrim(rtrim(A.[Product Id])), 1, charindex('.',ltrim(rtrim(A.[Product Id]))) - 1) =
substring(ltrim(rtrim(B.[Product Id])), 1, charindex('.',ltrim(rtrim(B.[Product Id]))) - 1)
)
Let me Know if it works. Merry Christmas from Santa 😀
December 21, 2013 at 3:47 am
Little better:
select A.[Product Id] as ProductId,
'-' as SubProductId,
(
select exp(sum(log(quantity))) from product_table B
where isprimary = 0
and patindex(ltrim(rtrim(A.[Product Id]))+'.%',B.[Product Id]) > 0
) as Value
from product_table A
where
A.isPrimary = 1
Union
select left(ltrim(rtrim(A.[Product Id])),charindex('.',ltrim(rtrim(A.[Product Id]))) - 1) as ProductId,
A.[Product Id] as SubProductId ,Null as Value
from product_table A
where A.isPrimary = 0
and not exists
(
Select *
from product_table B
where
substring(ltrim(rtrim(B.[Product Id])), charindex('.',ltrim(rtrim(B.[Product Id]))) + 1, len(ltrim(rtrim(B.[Product Id])))) >
substring(ltrim(rtrim(A.[Product Id])), charindex('.',ltrim(rtrim(A.[Product Id]))) + 1, len(ltrim(rtrim(A.[Product Id]))))
and B.isPrimary = 0
and
left(ltrim(rtrim(A.[Product Id])),charindex('.',ltrim(rtrim(A.[Product Id]))) - 1) =
left(ltrim(rtrim(B.[Product Id])),charindex('.',ltrim(rtrim(B.[Product Id]))) - 1)
)
bye bye
December 22, 2013 at 9:15 pm
ndiro (12/21/2013)
Little better:select A.[Product Id] as ProductId,
'-' as SubProductId,
(
select exp(sum(log(quantity))) from product_table B
where isprimary = 0
and patindex(ltrim(rtrim(A.[Product Id]))+'.%',B.[Product Id]) > 0
) as Value
from product_table A
where
A.isPrimary = 1
Union
select left(ltrim(rtrim(A.[Product Id])),charindex('.',ltrim(rtrim(A.[Product Id]))) - 1) as ProductId,
A.[Product Id] as SubProductId ,Null as Value
from product_table A
where A.isPrimary = 0
and not exists
(
Select *
from product_table B
where
substring(ltrim(rtrim(B.[Product Id])), charindex('.',ltrim(rtrim(B.[Product Id]))) + 1, len(ltrim(rtrim(B.[Product Id])))) >
substring(ltrim(rtrim(A.[Product Id])), charindex('.',ltrim(rtrim(A.[Product Id]))) + 1, len(ltrim(rtrim(A.[Product Id]))))
and B.isPrimary = 0
and
left(ltrim(rtrim(A.[Product Id])),charindex('.',ltrim(rtrim(A.[Product Id]))) - 1) =
left(ltrim(rtrim(B.[Product Id])),charindex('.',ltrim(rtrim(B.[Product Id]))) - 1)
)
bye bye
If you're gonna get all fancy and use higher math like logarithms and exponentials, then maybe something like this will work (maybe simpler yet?):
SELECT c.[Product Id], c.Quantity, c.SubProduct
FROM Product_Table a
CROSS APPLY
(
SELECT Quantity=ROUND(EXP(SUM(LOG(1.*Quantity))), 0)
,SubProduct=ProdID + '.' +
CAST(MAX(CAST(STUFF(b.[Product Id], 1, LEN(ProdID)+1, '') AS INT)) AS VARCHAR)
FROM Product_Table b
CROSS APPLY
(
SELECT LEFT(b.[Product Id], CHARINDEX('.', b.[Product Id] + '.')-1)
) c (ProdID)
WHERE IsPrimary = 0 AND a.[Product Id] = ProdID
GROUP BY ProdID
) b
CROSS APPLY
(
VALUES (a.[Product Id], NULL, b.Quantity),(a.[Product Id], b.SubProduct, 0)
) c ([Product Id], SubProduct, Quantity)
WHERE IsPrimary = 1;
In all seriousness, this approach is probably better than what I did the first time. For some reason the old logarithm/exponential trick eluded me.
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
December 22, 2013 at 11:54 pm
First, let it be known that I'm dead set against creating this type of output which basically forms an EAV. The output of the SELECT within ctePreAgg in the code below should be sufficient and is properly normalized.
I also believe that the original table should have separate columns for the ProductID and the SubProduct with a persisted calculated column to "assemble the parts" for display purposes.
That, notwithstanding, here's my simplistic shot at it. It only reads from the table 1 time and also avoids all non-SARGable joins as a result.
WITH ctePreAgg AS
( --=== Preaggregate the parts we need
SELECT [Product ID] = MAX(LEFT([Product ID],ISNULL(NULLIF(CHARINDEX('.',[Product ID])-1,-1),10)))
,SubProduct = MAX([Product ID])
,Quantity = ROUND(EXP(SUM(LOG(ISNULL(NULLIF(Quantity,0),1)))),0) --Create PRODUCT
FROM dbo.Product_Table
GROUP BY LEFT([Product ID],ISNULL(NULLIF(CHARINDEX('.',[Product ID])-1,-1),10))
) --=== Format the output including the (ugh!) line doubling using a CROSS APPLY
SELECT agg.[Product ID], ca.SubProduct, ca.Quantity
FROM ctePreAgg agg
CROSS APPLY
(
SELECT '', Quantity UNION ALL
SELECT SubProduct, 0
) ca (SubProduct,Quantity)
ORDER BY agg.[Product ID]
;
Results:
Product ID SubProduct Quantity
---------- ---------- ----------------------
P001 40
P001 P001.3 0
P002 41580
P002 P002.5 0
--Jeff Moden
December 23, 2013 at 12:38 am
Brilliant Jeff, simply brilliant!
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
December 23, 2013 at 1:40 am
dwain.c (12/23/2013)
Brilliant Jeff, simply brilliant!
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply