Solved

SQL First and last colour

Posted on 2014-12-11
8
98 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 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