SQL First and last colour
Evening all
I know someone will look at this and have me an answer in 2 seconds but I have brain freeze at the moment and can't for the life of me figure it out...(it's been one of those weeks)
So I have this SQL database of products and I want to get some of the data in a nice View that I can then use. I've sorted most of it using Stuff... for XML but not sure how to get the first and last colour to populate all the way down the view for the given product
Many thanks in advance
I know someone will look at this and have me an answer in 2 seconds but I have brain freeze at the moment and can't for the life of me figure it out...(it's been one of those weeks)
So I have this SQL database of products and I want to get some of the data in a nice View that I can then use. I've sorted most of it using Stuff... for XML but not sure how to get the first and last colour to populate all the way down the view for the given product
Many thanks in advance
Scott Pletcher
Would have to see the existing SQL to know specifically how to work that code in. Could write working stand-alone code, but that won't necessarily work in the context of a different query :-).
ASKER
Hi Scott
Here's what I have so far...
SELECT distinct OutTab.Product , OutTab.Colour ,
Colour_List =
STUFF ( ( SELECT ','+(InrTab.Colour)
FROM [Product] InrTab
WHERE InrTab.Product = OutTab.Product
AND inrtab.Colour_Sequence = Min(outtab.Colour_Sequence
ORDER BY InrTab.Product, inrtab.Colour_Sequence
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'
, 1,1,SPACE(0))
,
First_Colour = ???
,
Last_Colour = ???
FROM [Product] OutTab
GROUP BY OutTab.Product , OutTab.Colour
Here's what I have so far...
SELECT distinct OutTab.Product , OutTab.Colour ,
Colour_List =
STUFF ( ( SELECT ','+(InrTab.Colour)
FROM [Product] InrTab
WHERE InrTab.Product = OutTab.Product
AND inrtab.Colour_Sequence = Min(outtab.Colour_Sequence
ORDER BY InrTab.Product, inrtab.Colour_Sequence
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)'
, 1,1,SPACE(0))
,
First_Colour = ???
,
Last_Colour = ???
FROM [Product] OutTab
GROUP BY OutTab.Product , OutTab.Colour
;WITH cte_Products AS (
SELECT DISTINCT Product, Colour, Colour_Sequence
FROM [Product]
)
SELECT
p_outer.Product, p_outer.Colour,
STUFF((
SELECT ',' + p2.Colour
FROM cte_Products p2
WHERE
p2.Product = p_outer.Product
ORDER BY p2.Colour_Sequence
FOR XML PATH('')
), 1, 1, SPACE(0)) AS colour_list,
prod_colours_min_max.colou
FROM cte_Products p_outer
INNER JOIN (
SELECT Product,
MAX(CASE WHEN row_colour_min = 1 THEN Colour ELSE '' END) AS colour_min,
MAX(CASE WHEN row_colour_max = 1 THEN Colour ELSE '' END) AS colour_max
FROM (
SELECT Product, Colour,
ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Colour_Sequence ASC) AS row_colour_min,
ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Colour_Sequence DESC) AS row_colour_max
FROM cte_Products
) AS derived
GROUP BY Product
) AS prod_colours_min_max ON
prod_colours_min_max.Produ
ORDER BY p_outer.Product, p_outer.colour_sequence
SELECT DISTINCT Product, Colour, Colour_Sequence
FROM [Product]
)
SELECT
p_outer.Product, p_outer.Colour,
STUFF((
SELECT ',' + p2.Colour
FROM cte_Products p2
WHERE
p2.Product = p_outer.Product
ORDER BY p2.Colour_Sequence
FOR XML PATH('')
), 1, 1, SPACE(0)) AS colour_list,
prod_colours_min_max.colou
FROM cte_Products p_outer
INNER JOIN (
SELECT Product,
MAX(CASE WHEN row_colour_min = 1 THEN Colour ELSE '' END) AS colour_min,
MAX(CASE WHEN row_colour_max = 1 THEN Colour ELSE '' END) AS colour_max
FROM (
SELECT Product, Colour,
ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Colour_Sequence ASC) AS row_colour_min,
ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Colour_Sequence DESC) AS row_colour_max
FROM cte_Products
) AS derived
GROUP BY Product
) AS prod_colours_min_max ON
prod_colours_min_max.Produ
ORDER BY p_outer.Product, p_outer.colour_sequence
ASKER
Hi Scott
It works very well as a query but how do I stick that into a view as I don't think it'll allow the Order by clause in a sub query unless you use For XML?
It works very well as a query but how do I stick that into a view as I don't think it'll allow the Order by clause in a sub query unless you use For XML?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Slight mistake on my part. What I want in the view should look like below where for Prod02 the first colour is Black as the Colour _Sequence is 2 and the last colour should be Orange as the Colour _Sequence is 7. Also the Colour List should have been Black,Orange
Scott's query does this correctly but I'm not sure how to get it into a View
Scott's query does this correctly but I'm not sure how to get it into a View
>> Scott's query does this correctly but I'm not sure how to get it into a View <<
Please see my immediately previous post. Just remove the leading ; and the ending ORDER BY from my query.
Please see my immediately previous post. Just remove the leading ; and the ending ORDER BY from my query.
ASKER
You are a Steely-eyed missile man Scott. Much thanks!!