Solved

SQL First and last colour

Posted on 2014-12-11
8
93 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:ScottPletcher
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
;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
 

Author Comment

by:LukeFileWalker
Comment Utility
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
>> 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
Comment Utility
You are a Steely-eyed missile man Scott. Much thanks!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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

10 Experts available now in Live!

Get 1:1 Help Now