March 10, 2008 at 5:47 pm
Hi,
I have tables that store the users' consumption of different fruits like the following:
-------------------------------------------------------------------
CREATE TABLE #UserAccount (userID smallint IDENTITY NOT NULL, username nvarchar(10));
INSERT INTO #UserAccount VALUES ('Peter');
INSERT INTO #UserAccount VALUES ('Paul');
INSERT INTO #UserAccount VALUES ('John');
CREATE TABLE #Fruit (fruitID smallint IDENTITY NOT NULL, fruitName nvarchar(10));
INSERT INTO #Fruit VALUES ('Apple');
INSERT INTO #Fruit VALUES ('Banana');
INSERT INTO #Fruit VALUES ('Orange');
CREATE TABLE #Consumption(consumptionID smallint IDENTITY NOT NULL, userID smallint, fruitID smallint, consumptionTime smalldatetime);
INSERT INTO #Consumption VALUES (1, 1, '2008/01/15');
INSERT INTO #Consumption VALUES (3, 2, '2008/02/01');
INSERT INTO #Consumption VALUES (1, 2, '2008/01/29');
INSERT INTO #Consumption VALUES (2, 1, '2007/12/21');
INSERT INTO #Consumption VALUES (1, 1, '2007/12/31');
INSERT INTO #Consumption VALUES (3, 3, '2008/01/08');
INSERT INTO #Consumption VALUES (3, 3, '2008/01/15');
INSERT INTO #Consumption VALUES (3, 2, '2008/02/22');
-------------------------------------------------------------------
Is it possible to write a query that returns result in the following format?
2007 December Paul Apple(1)
2007 December Peter Apple(1)
2008 January John Orange(2)
2008 January Peter Apple(1) Banana(1)
2008 February John Banana(2)
Please note each user only occupies one row per month, with each fruit occupying one column and the number of consumption in brackets. So if a user eats 1 fruit in a month, that row will have 1 column for fruit, but if he eats 10 different kinds of fruit, there will be 10 columns.
Can this be done?
Thanks,
ywb
March 10, 2008 at 8:03 pm
What tool are you using to present the data? I would suggest you look at either
1. MS Excel pivot tables
2. Crystal Reports - Crosstab
3. SQL Server Reporting Services - Matrix
4. Your own code in the presentation layer.
This is most definitely a presentation issue.
With your result data
2007 December Paul Apple(1)
2007 December Peter Apple(1)
2008 January John Orange(2)
2008 January Peter Apple(1) Banana(1)
2008 February John Banana(2)
The column headings I assume are
YEAR, MONTH, NAME, FRUITS where FRUITS = 'apple(x), banana(y), orange(z), etc'
rather than
YEAR, MONTH, NAME, APPLE_QTY, ORANGE_QTY, BANANA_QTY, other fruits.....
If the latter then you really should do it in the presentation layer.
If the former then you could do it in SQL (but still probably shouldn't). Something like
...
Oh wait, you're using sql 7 or 2000... The code I was going to suggest did some fancy string concatenation for you but only works in 2005. You *could* use a cursor to build up your fruits string but that's really pushing the envelope of what you should do in T-SQL.
March 10, 2008 at 8:04 pm
I created the table by using a dummy table not a temp table that is why the tables dont have #
select datename(yyyy,c.consumptiontime) as 'Year',
CASE WHEN month(c.consumptiontime) = 1 THEN 'January'
WHEN month(c.consumptiontime) = 2 THEN 'February'
WHEN month(c.consumptiontime) = 3 THEN 'March'
WHEN month(c.consumptiontime) = 4 THEN 'April'
WHEN month(c.consumptiontime) = 5 THEN 'May'
WHEN month(c.consumptiontime) = 6 THEN 'June'
WHEN month(c.consumptiontime) = 7 THEN 'July'
WHEN month(c.consumptiontime) = 8 THEN 'August'
WHEN month(c.consumptiontime) = 9 THEN 'September'
WHEN month(c.consumptiontime) = 10 THEN 'October'
WHEN month(c.consumptiontime) = 11 THEN 'November'
WHEN month(c.consumptiontime) = 12 THEN 'December'
END as 'Month',u.username,
f.fruitname+'('+cast (count(f.fruitid) as varchar(5))+')' as 'Fruits Consumed'
from consumption c, useraccount u , fruit f
where c.userid = u.userid and c.fruitid = f.fruitid
group by datename(yyyy,c.consumptiontime),month(c.consumptiontime),
u.username,f.fruitname
order by datename(yyyy,c.consumptiontime),
month(c.consumptiontime)
NOTE: When a person consumed more than 1 fruit it will be displayed in another column...
"-=Still Learning=-"
Lester Policarpio
March 10, 2008 at 9:54 pm
This is a bit long.... you can analyze the flow of the script so that you can develope your own script and maybe enhance this one (i know this is not good enough please bear with a beginner like me :D) If any1 has a much easier approach please post it so that you can help others (like me) to learn new ideas...
--===================== Creation of Test Tables with Dummy data
CREATE TABLE UserAccount (userID smallint IDENTITY NOT NULL, username nvarchar(10));
INSERT INTO UserAccount VALUES ('Peter');
INSERT INTO UserAccount VALUES ('Paul');
INSERT INTO UserAccount VALUES ('John');
CREATE TABLE Fruit (fruitID smallint IDENTITY NOT NULL, fruitName nvarchar(10));
INSERT INTO Fruit VALUES ('Apple');
INSERT INTO Fruit VALUES ('Banana');
INSERT INTO Fruit VALUES ('Orange');
CREATE TABLE Consumption(consumptionID smallint IDENTITY NOT NULL, userID smallint, fruitID smallint, consumptionTime smalldatetime);
INSERT INTO Consumption VALUES (1, 1, '2008/01/15');
INSERT INTO Consumption VALUES (3, 2, '2008/02/01');
INSERT INTO Consumption VALUES (1, 2, '2008/01/29');
INSERT INTO Consumption VALUES (2, 1, '2007/12/21');
INSERT INTO Consumption VALUES (1, 1, '2007/12/31');
INSERT INTO Consumption VALUES (3, 3, '2008/01/08');
INSERT INTO Consumption VALUES (3, 3, '2008/01/15');
INSERT INTO Consumption VALUES (3, 2, '2008/02/22');
--====================
--==================== Declare Variables for the cursor
DECLARE @year varchar(20),@month varchar(20),@name varchar(50),@fruit varchar(20)
--==================== Create table where new data will be inserted
CREATE TABLE Display (Years char(4),Months int,Name varchar(30),Fruit_Consumed varchar(8000))
--=================== Create Cursor
DECLARE data CURSOR FOR
--===================Query to get the neccessary information
--=================== (if a user consumed another fruit it will still be displayed in the next line)
select datename(yyyy,c.consumptiontime) as 'Year',
month(c.consumptiontime) as 'Month',u.username,
f.fruitname+'('+cast (count(f.fruitid) as varchar(5))+')' as 'Fruit Consumed'
from consumption c, useraccount u , fruit f
where c.userid = u.userid and c.fruitid = f.fruitid
group by datename(yyyy,c.consumptiontime),month(c.consumptiontime),
u.username,f.fruitname
order by datename(yyyy,c.consumptiontime),
month(c.consumptiontime)
OPEN data
FETCH NEXT FROM data INTO @year,@month,@name,@fruit
WHILE(@@FETCH_STATUS = 0)
BEGIN
--=================== Test if year,month,username is equal to the values in the cursor
IF EXISTS (select * from display where years = @year and months = @month and name = @name)
BEGIN
--================== Update data inside the table to include the next fruit consumed
--================== so that other fruits consumed will be displayed beside the first fruit consumed
--================== not creating another column to display other fruits consumed by the same user
UPDATE display SET Fruit_Consumed = Fruit_Consumed+' '+@fruit
where (years = @year) and (months = @month) and (name = @name)
END
ELSE
--================== If values in @year,@month,@name doesn't match the data inside the table
--================== just insert the recent value of the variables in the tables
INSERT INTO Display VALUES (@year,@month,@name,@fruit)
FETCH NEXT FROM data INTO @year,@month,@name,@fruit
END
CLOSE data
DEALLOCATE data
--================== Last query which will display the desired result
select years as 'YEAR',
CASE
WHEN months = '1' THEN 'January'
WHEN months = '2' THEN 'February'
WHEN months = '3' THEN 'March'
WHEN months = '4' THEN 'April'
WHEN months = '5' THEN 'May'
WHEN months = '6' THEN 'June'
WHEN months = '7' THEN 'July'
WHEN months = '8' THEN 'August'
WHEN months = '9' THEN 'September'
WHEN months = '10' THEN 'October'
WHEN months = '11' THEN 'November'
WHEN months = '12' THEN 'December'
END as 'MONTHS',
name as 'USERNAME',fruit_consumed as 'FRUITS CONSUMED'
FROM display order by years
--================= Drop the test tables
DROP TABLE Display
DROP TABLE consumption
DROP TABLE fruit
DROP TABLE UserAccount
"-=Still Learning=-"
Lester Policarpio
March 11, 2008 at 9:33 am
Thanks, Lester!
It's great that I get the counts in the brackets now, but is there any way to have each fruit in it's own column? I'm not very good with SQL and this is the part I'm not sure if it's technically possible at all...
Thank you again for your help!
March 11, 2008 at 6:34 pm
Nice try, Lester...seriously... but ya just gotta make the shift from thinking in rows to thinking in columns. No more cursors, please... 😀
The following set based code replaces Lester's cursor and all the other stuff. I'll be back in a minute with the multicolumn part... and, yes, it will be SQL Server 2000 compatible...
--===== Replace cursor with set based code and some functions.
SELECT YEAR(c.ConsumptionTime) AS Year,
DATENAME(mm,c.ConsumptionTime) AS Month,
u.UserName,
f.FruitName +'(' + CAST(COUNT(f.FruitName) AS VARCHAR(10))+ ')' AS FruitQty
FROM #Consumption c
INNER JOIN #Fruit f
ON c.FruitID = f.FruitID
INNER JOIN #UserAccount u
ON c.UserID = u.UserID
GROUP BY YEAR(c.ConsumptionTime),
DATENAME(mm,c.ConsumptionTime),
u.UserName,
f.FruitName
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 6:38 pm
Ian Yates (3/10/2008)
Oh wait, you're using sql 7 or 2000... The code I was going to suggest did some fancy string concatenation for you but only works in 2005. You *could* use a cursor to build up your fruits string but that's really pushing the envelope of what you should do in T-SQL.
Heh... ok Ian, no supper for you... putting perfectly innocent folk up to making cursors... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 7:36 pm
The pain of CURSOR and the glory of JOINS hehehe thanks for the correction.
"-=Still Learning=-"
Lester Policarpio
March 11, 2008 at 7:42 pm
ywb (3/11/2008)
Thanks, Lester!It's great that I get the counts in the brackets now, but is there any way to have each fruit in it's own column? I'm not very good with SQL and this is the part I'm not sure if it's technically possible at all...
Thank you again for your help!
If you want to display each fruit in its own column Jeff's solution is good to go 🙂 and i think my first post will "somehow" do the work
"-=Still Learning=-"
Lester Policarpio
March 11, 2008 at 10:39 pm
Ok... just remember that you heard it from Ian first... this is the kind of stuff that you really want to do in the application if one is available. SQL Server 2000 makes it pretty tough to do this type of thing with any great amount of elegance. 2005 makes it a bit easier but, if you have an app in mind, do it there instead of SQL Server. For all those who don't have an app... consider Excel and it's Pivot power.
Here's how to do it all in SQL Server 2000... do notice that I did have to make the first displayed column a double whammy to preserve the sort order...
--=============================================================================
-- This is the original demo data posted and is NOT a part of the solution.
--=============================================================================
CREATE TABLE #UserAccount
(userID smallint IDENTITY NOT NULL, username nvarchar(10));
INSERT INTO #UserAccount VALUES ('Peter');
INSERT INTO #UserAccount VALUES ('Paul');
INSERT INTO #UserAccount VALUES ('John');
CREATE TABLE #Fruit
(fruitID smallint IDENTITY NOT NULL, fruitName nvarchar(10));
INSERT INTO #Fruit VALUES ('Apple');
INSERT INTO #Fruit VALUES ('Banana');
INSERT INTO #Fruit VALUES ('Orange');
CREATE TABLE #Consumption
(consumptionID smallint IDENTITY NOT NULL, userID smallint, fruitID smallint, consumptionTime smalldatetime);
INSERT INTO #Consumption VALUES (1, 1, '2008/01/15');
INSERT INTO #Consumption VALUES (3, 2, '2008/02/01');
INSERT INTO #Consumption VALUES (1, 2, '2008/01/29');
INSERT INTO #Consumption VALUES (2, 1, '2007/12/21');
INSERT INTO #Consumption VALUES (1, 1, '2007/12/31');
INSERT INTO #Consumption VALUES (3, 1, '2008/01/08');
INSERT INTO #Consumption VALUES (3, 1, '2008/01/08');
INSERT INTO #Consumption VALUES (3, 2, '2008/01/08');
INSERT INTO #Consumption VALUES (3, 3, '2008/01/08');
INSERT INTO #Consumption VALUES (3, 3, '2008/01/15');
INSERT INTO #Consumption VALUES (3, 2, '2008/02/22');
--=============================================================================
-- Solution starts here...
--=============================================================================
--===== Suppress the auto-display of rowcounts for appearance
SET NOCOUNT ON
--===== Prep a temp table to preserve the procedureal order of rows
CREATE TABLE #MyHead
(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Year Mo] CHAR(7),
Month CHAR(9),
UserName CHAR(10),
FruitName CHAR(10),
Rank INT DEFAULT 0)
--===== Declare a couple of local variables
DECLARE @MaxRank INT
DECLARE @PrevRank INT
DECLARE @PrevControl VARCHAR(50)
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
--===== Populate the temp table with our aggregated data
INSERT INTO #MyHead
([Year Mo],Month,UserName,FruitName)
SELECT STR(YEAR(c.ConsumptionTime),4)+STR(MONTH(c.ConsumptionTime),3) AS [Year Mo],
DATENAME(mm,c.ConsumptionTime) AS Month,
u.UserName,
f.FruitName +'(' + CAST(COUNT(f.FruitName) AS VARCHAR(10))+ ')' As FruitQty
FROM #Consumption c
INNER JOIN #Fruit f
ON c.FruitID = f.FruitID
INNER JOIN #UserAccount u
ON c.UserID = u.UserID
GROUP BY STR(YEAR(c.ConsumptionTime),4)+STR(MONTH(c.ConsumptionTime),3),
DATENAME(mm,c.ConsumptionTime),
u.UserName,
f.FruitName
ORDER BY [Year Mo],UserName,FruitQty
--===== Update the Rank Column by date, user name, and fruit (fruit is implied)
SELECT @PrevRank = 0,
@PrevControl = ''
UPDATE #MyHead
SET @PrevRank = Rank = CASE WHEN [Year Mo]+UserName = @PrevControl THEN @PrevRank +1 ELSE 1 END,
@PrevControl = [Year Mo]+UserName
FROM #MyHead WITH (TABLOCKX)
--===== Capture the MaxRank to use to count columns
SELECT @MaxRank = MAX(Rank)
FROM #MyHead
--===== Create the necessary Dynamic SQL to make the report
SELECT @SQL1 = 'SELECT [Year Mo],Month,UserName,' + CHAR(13)
SELECT @SQL2 = ISNULL(@SQL2+','+CHAR(13),'')
+ 'MAX(CASE WHEN Rank=' + STR(Number,3)
+ ' THEN FruitName ELSE '''' END) AS Fruit'
+ CAST(Number AS VARCHAR(3))
FROM Master.dbo.spt_Values
WHERE Type = 'P'
AND Number BETWEEN 1 AND @MaxRank
SELECT @SQL3 = CHAR(13) + 'FROM #MyHead GROUP BY [Year Mo],UserName,Month' + CHAR(13)
+ 'ORDER BY [Year Mo],Month,UserName'
--===== Run the report code
--PRINT @SQL1+@SQL2+@SQL3
EXEC (@SQL1+@SQL2+@SQL3)
... and now you know why people recommend not doing this formatting stuff in SQL Server (even though I didn't use a cursor).
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2008 at 11:17 am
Yeah! That does it!! 😀
Thanks, Jeff!!
March 12, 2008 at 11:43 am
Perfect... thanks for the feedback!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply