Help! SQL select statement for an uncommon task

Question

Help! SQL select statement for an uncommon task

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

picant (1/17/2011)
I guess I am missing how the business reason behind what you are doing isn't meshing with your fruit analogy.
Using analogies doesn't always help us help you solve your problem.
Let's pretend there is no business reason. Can anybody still solve the fruit task?

Purely as an exercise in Fruit-based word juggling...not intended to solve your problem... this works - it will perform 4 table scans though : 1 each for the attributes, and 2 for the "objects"...

(Sorry Jeff, I know you want to know the real story behind this so that you can help out with a decent solution to a bad design, but I love these little challenges and it was fun :w00t:)

Let's hope the conversation can continue - ignoring my stupidness 😛

-- use some table variables cos it's easier!

DECLARE @fruits TABLE (fruit VARCHAR (200))

DECLARE @tastes TABLE (taste VARCHAR (200))

DECLARE @colours TABLE (colour VARCHAR (200))

-- populate some test data

-- take your choice - fruit based or object names -

-- fruit based is easier to check

-- object names tests performance a tiny bit more - not a lot tho'

INSERT

@fruits

VALUES

('apple')

--select name from sys.objects

INSERT

@tastes

VALUES

('sweet'),

('bitter')

--select reverse(name) from sys.objects

INSERT

@colours

VALUES

('red'),

('green'),

('yellow')

--select stuff(name,1,len(name)/2,substring(name,len(name)/2,len(name))) from sys.objects

-- now for the manipulation

; WITH cte_tasty_fruits AS (

-- now randomly assign tastes to each fruit

-- and add a row number to use later in picking colours

SELECT

fruits.fruit,

tastes.taste,

rownum = row_number () OVER (PARTITION BY fruits.fruit ORDER BY @@spid)

FROM

@fruits AS fruits

CROSS APPLY

(SELECT

taste

FROM

@tastes) AS tastes

)

, cte_coloured_fruits AS (

-- now randomly assign colours to each fruit

-- and add a row number to use later in picking tastes

SELECT

fruits.fruit,

colours.colour,

rownum = row_number () OVER (PARTITION BY fruits.fruit ORDER BY @@spid)

FROM

@fruits AS fruits

CROSS APPLY

(SELECT

colour

FROM

@colours) AS colours

)

-- and now join the two sets together by fruit and row number

-- using a full outer join to get all the rows

SELECT

coalesce (tasty_fruits.fruit, coloured_fruits.fruit) AS fruit,

coloured_fruits.colour,

tasty_fruits.taste

FROM

cte_tasty_fruits AS tasty_fruits

FULL OUTER JOIN

cte_coloured_fruits AS coloured_fruits

ON

coloured_fruits.rownum = tasty_fruits.rownum

AND

coloured_fruits.fruit = tasty_fruits.fruit

Edit: Removed cte_colours - it was surplus to requirements

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 2

Here's one possible answer to the problem you requested. It does not, however, fix the fact that you're relating tables that have no relationship to each other...

--===== This just builds demonstrable test data

-- and is NOT a part of the solution.

CREATE TABLE #Fruit (FruitName VARCHAR(20));

CREATE TABLE #Color (ColorName VARCHAR(20));

CREATE TABLE #Taste (TasteName VARCHAR(20));

INSERT INTO #Fruit (FruitName)

SELECT 'Apple' UNION ALL

SELECT 'Tomato'

;

INSERT INTO #Color (ColorName)

SELECT 'Red' UNION ALL

SELECT 'Green' UNION ALL

SELECT 'Yellow'

;

INSERT INTO #Taste (TasteName)

SELECT 'Sweet' UNION ALL

SELECT 'Bitter'

;

--===== This solves the requested problem

WITH

cteFirstGrouping AS

(

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY f.FruitName ORDER BY NEWID()),

f.FruitName,

c.ColorName

FROM #Fruit f

CROSS JOIN #Color c

)

,

cteSecondGrouping AS

(

SELECT RowNum = ROW_NUMBER() OVER (ORDER BY NEWID()),

TasteName

FROM #Taste t

)

SELECT fg.FruitName, fg.ColorName, sg.TasteName

FROM cteFirstGrouping fg

LEFT OUTER JOIN cteSecondGrouping sg

ON fg.RowNum = sg.RowNum

;

--===== Housekeeping...

DROP TABLE #Fruit, #Color, #Taste

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Heh - no need for my apology for playing then - Jeff - you couldn't resist either!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

picant SSC Enthusiast Points: 109 More actions · Answer 4

...It also doesn't explain the actual business reason as to why we want to do this. Yes, I understand that you're trying to duplicate a method. I want to know "What on Earth were they thinking" when they designed this problem into the schema. There are two reasons for my curiosity... one is that I just want to know because I'm amazed that anyone would have a good reason for doing this and the other is to possibly suggest an alternative but I have to know the real reason before I or anyone else can suggest such a thing.

In other words you don't know how to solve problem described in the original post, do you?

Sorry, posted this before I saw 2 later posts.

picant SSC Enthusiast Points: 109 More actions · Answer 5

Not sure why my posts duplicate. I do click on the post button only once...

picant SSC Enthusiast Points: 109 More actions · Answer 6

Jeff Moden (1/17/2011)
Here's one possible answer to the problem you requested. It does not, however, fix the fact that you're relating tables that have no relationship to each other...
--===== This just builds demonstrable test data
-- and is NOT a part of the solution.
CREATE TABLE #Fruit (FruitName VARCHAR(20));
CREATE TABLE #Color (ColorName VARCHAR(20));
CREATE TABLE #Taste (TasteName VARCHAR(20));
INSERT INTO #Fruit (FruitName)
SELECT 'Apple' UNION ALL
SELECT 'Tomato'
;
INSERT INTO #Color (ColorName)
SELECT 'Red' UNION ALL
SELECT 'Green' UNION ALL
SELECT 'Yellow'
;
INSERT INTO #Taste (TasteName)
SELECT 'Sweet' UNION ALL
SELECT 'Bitter'
;
--===== This solves the requested problem
WITH
cteFirstGrouping AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY f.FruitName ORDER BY NEWID()),
f.FruitName,
c.ColorName
FROM #Fruit f
CROSS JOIN #Color c
)
,
cteSecondGrouping AS
(
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY NEWID()),
TasteName
FROM #Taste t
)
SELECT fg.FruitName, fg.ColorName, sg.TasteName
FROM cteFirstGrouping fg
LEFT OUTER JOIN cteSecondGrouping sg
ON fg.RowNum = sg.RowNum
;
--===== Housekeeping...
DROP TABLE #Fruit, #Color, #Taste

Tanks that's something I was looking for. Looks quite complex. It'll take me some time to understand how it works. Thanks again.

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 7

picant (1/17/2011)
Tanks that's something I was looking for. Looks quite complex. It'll take me some time to understand how it works. Thanks again.

Nope... not complex at all. The first half of the code is just to build a test table so that you can see that the rest of the code works.

The code that solves the problem begins with WITH.

The first CTE simply does a Cross-Join between Fruits and Colors to apply every color to every fruit. In real life, "colors" would be whatever your largest table of attributes is (the one with the most rows). I also add a simple row number that starts over at 1 for each fruit.

The second CTE does nothing nothing but add a row number to all the tastes. Why? Because we need something to join on because you have no other relationships between the tables.

The final SELECT simply does a left join on all the fruit/color combinations with the tastes because there are fewer tastes than fruit/color combinations. This allows us to each taste only once per fruit as you requested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)