October 25, 2013 at 7:27 am
I need to fill column values from preceding row if there is a null value in order to tie back to another table. Here's an image of what my query output produces...Column SubProduct need to carry the values down. So in my example where it says Color Reducer, I need rows 2 and 3 to also say Color Reducer and then so on for the rest of the values. The reason is because this data is actually split into two tables so when linking back all the chemicals to the SubProduct it produces many rows. I should only get 22 rows back and I get 22 rows for each SubProduct. Color Reducer should only have 3 rows, Fabric Crosslinker 2 etc. Can anyone tell me how to fill the values down the column? The original data is in a SQL table that has been scrapped from Excel Worksheets.
Query Grid Results
RecordGuidProduct StoreSubProductpImportChemicals
8690BA4ARaggedy DollKB ToysColor Reducer1None
8690BA4A 7n-propyl Alcohol
8690BA4A 6Methanol
8690BA4AZX-1L KB ToysFabric Crosslinker1Alkanolamine chelate of zirconium alkoxide
8690BA4A 9Polysaccharide blend
8690BA4AWG-3ZL KB ToysGellant 1Petroleum Distillate blend
8690BA4ASCC-5L KB ToysPlastic Control1Tetramethylammonium Chloride
8690BA4A 4Diethylenetriamine Alkylbenzene Sulfate
8690BA4A 2Benzene 1-1-oxybis-, tetrapropylene
8690BA4A 8Napthalene
8690BA4A 5Heavy Aromatic Solvent Naphtha
8690BA4A 3Diethylenetriamine
8690BA4A 12-Ethylhexanol
8690BA4ANE-6 KB ToysCleaner 1Isopropal Alcohol
QUERY
with Ingredients as (
select
ExcelRow startIngredients
, RecordGuid
from .dbo.ExcelRows
where len(Chemicals) >0
)
select
er.RecordGuid,
ltrim(rtrim (er.COLUMNA ))Product,
er.ColumnB Store,
er.ColumnC SubProduct,
ROW_Number ( ) OVER (PARTITION BY COLUMNA,COLUMNC order by COLUMNC) AS pImport,
ColumnD Chemicals
from dbo.ExcelRows ER with (nolock)
left join Ingredients
on Ingredients.RecordGuid = er.RecordGuid
where len(ColumnD) >=1
and Ingredients.startIngredients < ExcelRow
and er.RecordGuid = '8690BA4A-56DD-4C53-A924-4FA04407A345'
group by er.RecordGuid
,ColumnA
,ColumnB
,ColumnC
,ColumnD
October 25, 2013 at 7:45 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
Also, be wary of that NOLOCK hint. It can produce some very nasty results.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
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/
October 25, 2013 at 8:23 am
If you're really using SQL Server 2012, you should take a look at the LAG function.
For better answers, please take Sean's advice.
October 25, 2013 at 9:03 am
Thanks for the replies. My image of the results got pushed together on submit because I had it all laid out in table format. The LAG function does look like it does what I want only it carries it down only 1 row and some of my results might have 5 empty rows
Carrier Carrier
Proppant Proppant
Proppant Proppant
Biocide Biocide
acid buffer acid buffer
Break fluid Break fluid
Break fluid
Break fluid Break fluid
October 25, 2013 at 9:57 am
LAG value can go any rows back. That's the second parameter of the function. Check example C on the LAG documentation.
October 25, 2013 at 10:08 am
Maybe you could look on this great article from Jeff Moden. It might help you on your solution. It might not give you the exact query but if you understand it, you can achieve your expected results.
October 27, 2013 at 7:06 pm
Luis Cazares (10/25/2013)
LAG value can go any rows back. That's the second parameter of the function. Check example C on the LAG documentation.
Unfortunately the LAG function goes back a fixed number of rows and what you want seems to go back a variable number of rows.
Using a Window Frame (my first thought) and the Running Totals solution as suggested by Luis (referring you to Jeff's article) are also problematic because from what I can see, there is nothing in the data that ties (for example) "Raggedy Doll" to the two following rows "7 n-propyl Alcohol" and "6 Methanol" in order to distinguish those sub-rows from the sub-row of "ZX-1L" (which is "9 Polysaccharide blend").
To put it another way, you need an ordering mechanism linking your primary rows to your sub-rows. And you may be able to get that in some way, shape or form out of your original query.
To help us help you better though, you really need to post your data in some consumable form. Here's a partial of what I mean.
WITH SampleData (RecordGuid, Product, Store, SubProduct, pImport, Chemicals) AS
(
SELECT '8690BA4A','Raggedy Doll','KB Toys','Color Reducer','1','None'
UNION ALL SELECT '8690BA4A','7 n-propyl Alcohol', NULL, NULL, NULL, NULL
UNION ALL SELECT '8690BA4A','6 Methanol', NULL, NULL, NULL, NULL
UNION ALL SELECT '8690BA4A','ZX-1L','KB Toys','Fabric Crosslinker','1','Alkanolamine chelate of zirconium alkoxide'
UNION ALL SELECT '8690BA4A','9 Polysaccharide blend', NULL, NULL, NULL, NULL
--UNION ALL SELECT '8690BA4A WG-3ZL KB Toys Gellant 1 Petroleum Distillate blend
--UNION ALL SELECT '8690BA4A SCC-5L KB Toys Plastic Control 1 Tetramethylammonium Chloride
--UNION ALL SELECT '8690BA4A 4 Diethylenetriamine Alkylbenzene Sulfate
--UNION ALL SELECT '8690BA4A 2 Benzene 1-1-oxybis-, tetrapropylene
--UNION ALL SELECT '8690BA4A 8 Napthalene
--UNION ALL SELECT '8690BA4A 5 Heavy Aromatic Solvent Naphtha
--UNION ALL SELECT '8690BA4A 3 Diethylenetriamine
--UNION ALL SELECT '8690BA4A 1 2-Ethylhexanol
--UNION ALL SELECT '8690BA4A NE-6 KB Toys Cleaner 1 Isopropal Alcohol
)
SELECT *
FROM SampleData;
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
October 27, 2013 at 7:13 pm
Of course, if you can guarantee the maximum number of sub-rows that there are (my assumption below is 2, controlling the number of LAGs to COALESCE), you may be able to do something like this:
WITH SampleData (RecordGuid, Product, Store, SubProduct, pImport, Chemicals) AS
(
SELECT '8690BA4A','Raggedy Doll','KB Toys','Color Reducer','1','None'
UNION ALL SELECT '8690BA4A','7 n-propyl Alcohol', NULL, NULL, NULL, NULL
UNION ALL SELECT '8690BA4A','6 Methanol', NULL, NULL, NULL, NULL
UNION ALL SELECT '8690BA4A','ZX-1L','KB Toys','Fabric Crosslinker','1','Alkanolamine chelate of zirconium alkoxide'
UNION ALL SELECT '8690BA4A','9 Polysaccharide blend', NULL, NULL, NULL, NULL
--UNION ALL SELECT '8690BA4A WG-3ZL KB Toys Gellant 1 Petroleum Distillate blend
--UNION ALL SELECT '8690BA4A SCC-5L KB Toys Plastic Control 1 Tetramethylammonium Chloride
--UNION ALL SELECT '8690BA4A 4 Diethylenetriamine Alkylbenzene Sulfate
--UNION ALL SELECT '8690BA4A 2 Benzene 1-1-oxybis-, tetrapropylene
--UNION ALL SELECT '8690BA4A 8 Napthalene
--UNION ALL SELECT '8690BA4A 5 Heavy Aromatic Solvent Naphtha
--UNION ALL SELECT '8690BA4A 3 Diethylenetriamine
--UNION ALL SELECT '8690BA4A 1 2-Ethylhexanol
--UNION ALL SELECT '8690BA4A NE-6 KB Toys Cleaner 1 Isopropal Alcohol
)
SELECT RecordGuid, Product, Store
,SubProduct=COALESCE(SubProduct
,LAG(SubProduct, 1) OVER (ORDER BY (SELECT NULL))
,LAG(SubProduct, 2) OVER (ORDER BY (SELECT NULL)))
,pImport,Chemicals
FROM SampleData;
But do you see where I have ORDER BY (SELECT NULL)? This is what I mean by needing some method to guarantee ordering. The results my example produce wouldn't be guaranteed.
Performance of this with more than 2 LAGs though is probably going to be in the pooper.
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
October 27, 2013 at 9:15 pm
Thank you for your replies. I will post a query tomorrow in make table format for all to see. And yes the rows can be anywhere from 1 chemical to whatever, no fixed end point. However I do have a field I can use for order by and I'll be sure and include that in my make table statement.
I appreciate the help, this is beyond my knowledge and I've been wanting to pull my hair out, lol. I've looked at CTE, lag, lead, sub querying joining back. I looked at SSIS but as I have no experience with it that didn't help. So I really appreciate the help.
October 27, 2013 at 9:27 pm
kbenton 6603 (10/27/2013)
Thank you for your replies. I will post a query tomorrow in make table format for all to see. And yes the rows can be anywhere from 1 chemical to whatever, no fixed end point. However I do have a field I can use for order by and I'll be sure and include that in my make table statement.I appreciate the help, this is beyond my knowledge and I've been wanting to pull my hair out, lol. I've looked at CTE, lag, lead, sub querying joining back. I looked at SSIS but as I have no experience with it that didn't help. So I really appreciate the help.
If you've got a column you can order by, likely the easiest thing for you will be to use a Window Frame, although it may not be the fastest. Likely a Quirky Update would be faster for such a "data smear" (I heard Jeff Moden call this type of thing by this name once).
You might want to take a look at this article because it compares Window Frame with multiple lags vs. the Quirky Update from a performance perspective, albeit for a different problem.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply