Until now I used Access as Database Manager, but I had to migrate all my DBs to a SQL server.
Now I just use Access as Front-End and [Microsoft SQL Server Management Studio] as Back-End.
The SQL Server is 14 something...
But that's not the actuel problem here.
I made a sample DB (in Access) because of our data confidentiality...
The main table (tblCaddy) is the shopping caddy which may contain none or more vegetables (tblVegetables), fruits (tblFruits) and/or drinks (tblDrinks).
How many vegetables, fruits and/or drinks the caddy contains, I have the 3 intermediary tables (tblVegetables_in_Caddy, tblFruits_in_Caddy, tblDrinks_in_Caddy).
I already tried lots of things, read plenty of web pages about Pivot, but until now I'm still digging in the dark...
What I want to archieve is a query which will show all the Caddy content per CaddyOwner by row, like in the attached picture (done with Excel).
The number of columns will change depending of how many items a user has in his Caddy.
The last column ('Code', orange color) is optional, but if someone knows how to do it, I would be gratefull for that. The 'Code' is a concatenation of the number of Vegetables (Vn+1), number of Fruits(Fn+1) and number of Drinks (Dn+1) separated by an underscore ('_').
Does someone have an idea how to solve this problem?
Hello Yosiasz,
thank you for your interest for my situation
The highest maximum number of items will never exced 30 items (10 Fruits, 10 Vegetables, 10 Drinks).
The maximum I reached until today is 20 items.
Thank you
As I said, the DB is a test DB because of the confidentiality and so on...
The real DB has nothing to do with shopping.
Don't bother analysing this test DB structure or fake content.
You can do this but it requires some steps. As you are a beginner I think we are a little bit worried that we giving you the right answer but the wrong solution Based on our experience your table is not needed to solve any business requirement. But as it is dummy data and you want an answer I have created a little example. If you need an exact end result as pictured, please create the code so I can use it.
DROP TABLE IF EXISTS #Owner;
DROP TABLE IF EXISTS #Fruit;
CREATE TABLE #Owner (OwnerID int, OwnerName varchar(20));
INSERT INTO #Owner(OwnerID, OwnerName)
SELECT 1, 'Max'
UNION ALL
SELECT 2, 'John';
CREATE TABLE #Fruit (Fruit varchar(20), ownerID int);
INSERT INTO #Fruit (Fruit, ownerID)
SELECT 'Banana',1
UNION ALL
SELECT 'Banana',2
UNION ALL
SELECT 'Apple',2;
WITH FruitOwner AS
(
SELECT
OwnerName,
Fruit,
ROW_NUMBER() OVER (PARTITION BY OwnerName ORDER BY Fruit) AS RowNumber
FROM #Owner O
INNER JOIN #Fruit F
ON O.OwnerID=F.ownerID
) , CodeFruit AS
(
SELECT
OwnerName,
[1] AS F_1,
[2] AS F_2,
[3] AS F_3,
CASE
WHEN [1] IS NULL THEN 'F0'
WHEN [2] IS NULL THEN 'F1'
WHEN [3] IS NULL THEN 'F2'
END AS CodeFruit
FROM
(
SELECT
OwnerName,
Fruit,
Rownumber
FROM
FruitOwner
) P
PIVOT (
MAX(Fruit) FOR ROWNUMBER IN ([1],[2],[3])
) tbl
)
SELECT * FROM CodeFruit;
If you're interested in an alternative data structure:
I realize you may not have any control over the existing DB schema, and if that's the case, you will struggle to deliver pivoted results. The example you provided, if it is similar to your actual schema, is artificially separating attributes/fruits/drinks/veggies into different tables, when they could be in a single EAV structure, and then a single CaddyAttributeLink table. Any additional attributes won't require a new table, they are simply new rows in the EAV table.
(@yosiasz already commented on needing new tables for new products, your replies didn't address this)
Also, dynamic pivoting is not well supported in SQL Server. MS Access is much better for this (the TRANSFORM statement), or even better, a reporting feature like MS Access Reports or SQL Server Reporting Services. (or an Excel pivot table)
I am saying what if you start selling: candies, sugar, milk etc