Solved

SQL First and last colour

Posted on 2014-12-11
8
95 Views
Last Modified: 2014-12-11
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

 This is what my DB looks like
This is what I want in my View
0
Comment
Question by:LukeFileWalker
  • 4
  • 4
8 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40494754
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 :-).
0
 

Author Comment

by:LukeFileWalker
ID: 40494844
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40494940
;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.colour_min, prod_colours_min_max.colour_max
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.Product = p_outer.Product
ORDER BY p_outer.Product, p_outer.colour_sequence
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:LukeFileWalker
ID: 40495048
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?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40495085
You can remove the ORDER BY at the end, I just threw that in there (I like to see things in logical order :-) ).

And of course remove the ; from before the WITH:

CREATE VIEW <view_name> AS
WITH cte_Products AS (
...
)
...
--ORDER BY --remove this
0
 

Author Comment

by:LukeFileWalker
ID: 40495096
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

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40495103
>> 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.
0
 

Author Closing Comment

by:LukeFileWalker
ID: 40495108
You are a Steely-eyed missile man Scott. Much thanks!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlServer no dupes 25 34
sql server insert 12 28
sql how to count case when 4 14
Help in Bulk Insert 9 29
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now