Version: Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
I need to select a varying number of values from a table where a certain column is equal to a parameter and a certain column is LIKE 'String1' or 'String2'.
I have created a stored procedure that is returning the MAX and MIN strings, but naturally this method is not dynamic.
I have tried the following query which says it completes successfully, but does not return any results.
SELECT UPC, PartNum, PartDesc
FROM dbo.table
WHERE UPC = @upc
GROUP BY UPC, PartNum, PartDesc
HAVING PartDesc in ('%RED%','%BLUE%')
ORDER BY PartDesc;
Example table:
ID UPC PartNum PartDesc
-------------------------------------------
1 123 543 Red1
2 123 345 Blue1
3 123 654 Red2
4 123 765 Blue2
I need to pass a parameter to a stored procedure as @upc from an application.
Where it will find any PartDesc that are like '%RED%' or '%BLUE%' AND where the UPC = @upc. Then store the Part#(s) found in a new table to be queried later.
Created Table From Stored Procedure:
ID UPC Red1 Red2 Blue1 Blue2
----------------------------------------------------------
1 123 543 654 345 765
There can be any number or combination of "Red" or "Blue" per UPC number. i.e., Some UPC numbers may only have two "Red" parts and one "Blue" part and others may only have two "Red" parts and no "Blue" parts. Maybe five "Red" parts and ten "Blue" parts.
How do would I write the query that will store the varying number of found results to a new table in a stored procedure?
Edit It seems as though the PIVOT function should be used, but I am unsure of how to use the required aggregate in my scenario. I don't need to pivot on the "SUM" of PartDesc or any other column for that matter. Perhaps a dynamic Pivot?
EDIT Based on Corgi's recommendation. Also, showing my work.
DECLARE @upc As varchar(13)
DECLARE @Red1 As nvarchar(100) = CASE
WHEN
(
SELECT MIN(PartNum) FROM dbo.table
WHERE PartDesc LIKE '%RED%' AND UPC = @upc
) IS NOT NULL THEN
(
SELECT MIN(PartNum) FROM dbo.table
WHERE PartDesc LIKE '%RED%' AND UPC = @upc
)
ELSE 'Not Found'
END
DECLARE @Red2 As nvarchar(100) = CASE
WHEN
(
SELECT MAX(PartNum) FROM dbo.table
WHERE PartDesc LIKE '%RED%' AND UPC = @upc
) IS NOT NULL THEN
(
SELECT MAX(PartNum) FROM dbo.table
WHERE PartDesc LIKE '%RED%' AND UPC = @upc
)
ELSE 'Not Found'
END
DECLARE @Blue1 As nvarchar(100) = CASE
WHEN
(
SELECT MAX(PartNum) FROM dbo.table
WHERE PartDesc LIKE '%BLUE%' AND UPC = @upc
) IS NOT NULL THEN
(
SELECT MAX(PartNum) FROM dbo.table
WHERE PartDesc LIKE '%BLUE%' AND UPC = @upc
)
ELSE 'Not Found'
END
;WITH MostColumns AS
(
SELECT UPC, @Red1 As Part1, @Red2 As Part2, @Blue1 As Part3
FROM (SELECT UPC, PartNum, PartDesc
FROM dbo.table) AS source
PIVOT
(MIN(PartNum) FOR PartDesc IN ([Part1], [Part2], [Part3])) AS pvt
)
SELECT MIN(p.ID) AS ID, p.UPC, mc.Part1, mc.Part2, mc.Part3
INTO MyNewTable
FROM dbo.table p
INNER JOIN MostColumns mc ON p.UPC = mc.UPC
GROUP BY p.UPC, mc.Part1, mc.Part2, mc.Part3
Result:
ID UPC Part1 Part2 Part3
2876 123 Not Found Not Found Not Found
2758 213 Not Found Not Found Not Found
2321 312 Not Found Not Found Not Found
802 321 Not Found Not Found Not Found
868 132 Not Found Not Found Not Found
This is the correct format, but no cigar. I know for a fact, that all of my UPCs contain atleast one Red1 part. For some reason, it did not find any of the parts.
EDIT--ANSWER @Corgi After more research on dynamic pivots I arrived at this solution. I will still need to build on it to make it operate the way I need it to. Although, those are not relevant to this question. Thank you @bluefeet for your answer in this post. SQL Dynamic Pivot
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ','
+ QUOTENAME('Part_' + cast(rn as varchar(10)))
from dbo.table
cross apply
(
select row_number() over(partition by UPC order by PartNum) rn
from dbo.table
) x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT UPC, ' + @cols + ' from
(
select UPC, PartNum,
''Component_''
+ cast(row_number() over(partition by UPC order by PartNum) as varchar(10)) val
from dbo.table
) x
pivot
(
max(PartNum)
for val in (' + @cols + ')
) p '
execute(@query)
Your observation about PIVOT is on track, but you can't actually use PIVOT without specifying the output column names (that is, the values from PartDesc). It sounds like, because there can be a varying number of these PartDesc values, the closest you can get would be to find all the values:
SELECT DISTINCT t.PartDesc
FROM MyTable t
WHERE t.PartDesc LIKE '%Red%' OR t.PartDesc LIKE '%Blue%'
Then you can use the values to build your query. If you really have to have the query be dynamic, you would need to construct a query string to use with something like sp_executesql. The way you would create the table from the output is by using SELECT... INTO within your dynamic query.
The PIVOT syntax you need, combined with SELECT... INTO, might look something like:
;WITH MostColumns AS
(
SELECT UPC, Red1, Red2, Blue1, Blue2
FROM (SELECT UPC, PartNum, PartDesc
FROM dbo.table) AS source
PIVOT
(MIN(PartNum) FOR PartDesc IN ([Red1], [Red2], [Blue1], [Blue2])) AS pvt
)
SELECT MIN(p.ID) AS ID, p.UPC, mc.Red1, mc.Red2, mc.Blue1, mc.Blue2
INTO MyNewTable
FROM dbo.table p
INNER JOIN MostColumns mc ON p.UPC = mc.UPC
GROUP BY p.UPC, mc.Red1, mc.Red2, mc.Blue1, mc.Blue2
The MostColumns common table expression is there because it doesn't work well to have ID in your original query -- it's an "extra" column that is not part of the pivot.