Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL First and last colour

Posted on 2014-12-11
8
Medium Priority
?
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 70

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 70

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

610 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