CODEWITHSUNDEEP
sqlsql-serverlistt-sql
usertfwr
usertfwr

Reputation: 319

T-SQL, Make a list

I am using T-SQL and Microsoft SQL Server. I have different tables and I would like to get a specific results like in the example

I have 3 "recipes" tables which contain different ingredients:

|Recipe X|       |Recipe Z|       |Recipe V|
__________       __________       __________
A                A                B
B                D                E
C                F                C

I also have a table Week:

Mon||Tue||Wed||Thu||Fri||Sat||Sun
---------------------------------
X  | Z |  X  | V  | Z  | V  | V

For each day of the week there is a recipe. I would like to find which day have the recipe and the position (for example the position of monday is 1, tuesday is 2...)

So it will looks like

Recipe X: (1, 3)

Recipe Z: (2, 5)

Recipe V: (4, 6, 7)

I didn't find the way to do list in T-SQL If there is another way to do like this?

Thanks

Upvotes: 2

Views: 227

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

The table structure you're using makes the query somewhat complex, you should really look into @podiluska's recommendation to normalize your tables.

That said, it's doable using a cte (or Common Table Expression) that basically "does the normalization at runtime", and a regular query to get the result;

WITH cte AS (
  SELECT '1' day, Mon Recipe FROM Week UNION ALL
  SELECT '2' day, Tue Recipe FROM Week UNION ALL
  SELECT '3' day, Wed Recipe FROM Week UNION ALL
  SELECT '4' day, Thu Recipe FROM Week UNION ALL
  SELECT '5' day, Fri Recipe FROM Week UNION ALL
  SELECT '6' day, Sat Recipe FROM Week UNION ALL
  SELECT '7' day, Sun Recipe FROM Week
)
SELECT DISTINCT 'Recipe_' + Recipe AS Recipe,
   Days=STUFF((SELECT ','+day FROM cte WHERE Recipe=A.Recipe FOR XML PATH('')), 
                                                                  1 , 1 , '' )
FROM 
   cte A

An SQLfiddle to test with.

Upvotes: 1

podiluska
podiluska

Reputation: 51514

Yes. Normalise your week table to have day, recipe and position fields.

Mon X 1
Tue Z 2

then it is a simple query

select position
from table
where recipe = 'z'

you could calculate the position from the day name, or vice versa, ifyou wanted.

Upvotes: 1

Related Questions