Creating a matrix using two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a matrix using two tables

Author

Topic

VTC
Starting Member

9 Posts

Posted - 2011-10-08 : 21:53:48

I'm pretty new to SQL and wanted to try using it to complete a menial task that I'd typically have a subordinate do manually.

As an example of what I'm trying to do, say I have two tables: the first containing a variable list of food items along with information associted with each item, and a second with a person column and food item column. The foods in the first table aren't necessarily referenced in the second, but all the foods in the second are present in the first.

What I'm looking to do is create a matrix with the food items from first table as the column names, the people from the second on the rows, marking - let's say with an 'X' - where the two cooincide using the records from the second table.

I'm fluent in some other languages, but I'd like to try to do this all in SQL, if possible.

Many thanks in advance,

Vigo the Carpathian

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-09 : 06:57:13

you are taking about relations between the 2 tables ,

this can be done using inner joins .

lets say first table : fooditems contains ID and Name fields

second table :people contains ID , name , age , fooitemsID

so the query will be

select fooditems.ID , fooditems.name , people.name , people.age
from fooditems inner join people
on fooditems.ID = people.fooditemsID

for more information about inner join and how it works

http://www.w3schools.com/sql/sql_join_inner.asp

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-10-09 : 10:12:25

Don't think about getting the 'X' value in a query joining those tables and using a group by clause. This is best accomplished in the matrix.

Produce the dataset by just the joining query, only returning the ungrouped data. Just rows. Select only the columns you want. Some might like to put the group in the initial query, but this will omit some of the data.

Assuming your 'X' with be a count, and you want to know how many customers bought which items, over a time window for, say, last month. Make the matrix where the customer name field down the left of the matrix and the list of items goes across the top. The 'X' will show a count of which customer bought what. Set parameters if the user want to see a particular set of customers and a set of items, and the time window.

You'll see a load of zeros in there, which is useful, and shows your trend for unpopular items. You might want to set a threshold to only show results where the count is more than zero, or more than 10, etc... which uses the having clause, but a matrix expression will handle that. Don't put it in the dataset query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-09 : 13:46:02

in t-sql, you can achieve it like below though


DECLARE @FoodLIst varchar(2000),@Sql varchar(8000)
SET @FoodLIst='[' + (SELECT '],[' + FoodItem FROM FoodTable FOR XML PATH('')) + ']'

SET @Sql='SELECT person,' + @FoodLIst + 
' FROM
(
SELECT ft.fooditem,p.person
FROM [Food Table] ft
LEFT JOIN Person p
ON p.[fooditem] = ft.[fooditem]
)t
PIVOT (MAX(CASE WHEN p.foodItem IS NOT NULL THEN 'X' ELSE '' END) FOR ft.fooditem IN (' + @FoodLIst + '))p'

EXEC(@Sql)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VTC
Starting Member

9 Posts

Posted - 2011-10-09 : 19:15:42

Thanks, everyone who replied. Paultech, mikebird, I don't think what you're describing is going to give the results needed. The inner join will result in a table that has the people fields (name, age, ID) and the food item name field from the other table. I need something that will result in the food item names from the food table as the field names in the resulting table. I'm shooting for something similar to this (forum text formating gets wonky, so I've set it up as a csv table, the first row being the field name):

PersonName,Apple,Banana,Coconut,Date
Alice,X,X,,
Bob,,X,X,
Carl,X,,X,
Daryl,,X,,X
Earl,,,X,X
Frank,X,X,,X

I think visakh16 understands what I'm looking to do, but I'm having some problems getting the syntax just right. I created two test tables with fields matching the ones in the query just to test and I'm getting the error:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'X'.

I did some adding/removing of single quotes because I initially thought it wasma matter of escaping out the character, but I didn't make any progress. Like I said, I'm new to SQL, so it was just a stab in the dark; I'm probably way off base from the real cause. Any of you have an idea what may be the cause?

Thanks,

Vigo the Carpathian

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-09 : 20:41:55

quote:
I'm getting the error:

Msg 102, Level 15, State 1, Line

Please post your query so that we can help to identity where is the error coming from.

KH
[spoiler]Time is always against us[/spoiler]

VTC
Starting Member

9 Posts

Posted - 2011-10-09 : 21:02:19

Hi khtan. The query is the one that visakh16 posted. I've added a USE at the beginning to hit the right DB, but made no other changes.

Vigo the Carpathian

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-09 : 21:20:33

change this part


PIVOT (MAX(CASE WHEN p.foodItem IS NOT NULL THEN ''X'' ELSE '''' END) FOR ft.fooditem IN (' + @FoodLIst + '))p'

KH
[spoiler]Time is always against us[/spoiler]

VTC
Starting Member

9 Posts

Posted - 2011-10-09 : 21:38:36

AH HA! Great to know I was on the right track. It appears that I am learning! I had actually tried that back when I was doing some trial and error. It still gave a syntax error, but changed the location of it. Specifically, after adding the extra 's you noted, the query returns:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'CASE'.

Just to confirm that I've got the right query that you are suggesting, what I have now is:

DECLARE @FoodLIst varchar(2000),@Sql varchar(8000)
SET @FoodLIst='[' + (SELECT '],[' + FoodItem FROM FoodTable FOR XML PATH('')) + ']'

SET @Sql='SELECT person,' + @FoodLIst + 
' FROM
(
SELECT ft.fooditem,p.person
FROM [Food Table] ft
LEFT JOIN Person p
ON p.[fooditem] = ft.[fooditem]
)t
PIVOT (MAX(CASE WHEN p.foodItem IS NOT NULL THEN ''X'' ELSE '''' END) FOR ft.fooditem IN (' + @FoodLIst + '))p'

EXEC(@Sql)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-09 : 21:51:22

i haven't gone through the query logic yet, but quick look noticed that you have "FoodTable" and "[Food Table]" in your query. Which is the correct table name ? with or without space in between ?

quick tip for debugging. use PRINT statement to print out the dynamic sql. Add this before the exec(@Sql) to print out the query for inspection

PRINT @Sql

KH
[spoiler]Time is always against us[/spoiler]

VTC
Starting Member

9 Posts

Posted - 2011-10-10 : 20:20:10

Nice catch on the typo. That's something that got copied in from the originally suggested query from visakh16, but I really should have caught. The correct name is FoodTable.

Thanks for the tip about PRINT.

Using it, I was able to get closer, but I've hit another spot where I'm not quite sure how to proceed. I was able to determine that the lines

SET @FoodList='[' + (SELECT + '],[' + FoodItem FROM FoodTable FOR XML PATH('')) + ']'

and

SET @Sql='SELECT person,' + @FoodList +

were producing a resulting query of

SELECT person,[],[Apple],[Grapes],[Kiwi],[Pear],[Starfruit],[NotUsed1],[NotUsed2]

(Note: [Apple],[Grapes],[Kiwi],[Pear],[Starfruit],[NotUsed1],&[NotUsed2] are the Food Item names from my test table)

which understandably returns the error

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

In addition, the PIVOT line that is created by @FoodList is

PIVOT (MAX(CASE WHEN p.foodItem IS NOT NULL THEN 'X' ELSE '' END) FOR ft.fooditem IN ([],[Apple],[Grapes],[Kiwi],[Pear],[Starfruit],[NotUsed1],[NotUsed2]))

which returns the error

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'CASE'.

The first error is fairly straight forward - it doesn't like [] in the SELECT since there's nothing in it. And the best I can guess about the syntax error is that the empty [] in the "ft.fooditem IN ()" section after the CASE is causing it to be thrown. (Please correct me if this is wrong.) So I did some rearranging of where the []s are located to get rid of the blank field. I changed the following lines:

SET @FoodList=(SELECT + '],[' + FoodItem FROM FoodTable FOR XML PATH('')) + ']'

SET @Sql='SELECT [person' + @FoodList +

This results in

SELECT [person],[Apple],[Grapes],[Kiwi],[Pear],[Starfruit],[NotUsed1],[NotUsed2]

for the SELECT part, which eliminates the first error, but the PIVOT line is only changed to

PIVOT (MAX(CASE WHEN p.foodItem IS NOT NULL THEN 'X' ELSE '' END) FOR ft.fooditem IN (],[Apple],[Grapes],[Kiwi],[Pear],[Starfruit],[NotUsed1],[NotUsed2]))

which results in the open ] causing the same syntax error.

Is there an easy means for cleaning up the []s in both the SELECT and CASE sections? I know I'm really asking a lot and I appreciate everyone taking the time to help me learn this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 20:56:39

can you show us the result of print @Sql just before the exec (@Sql) ?

KH
[spoiler]Time is always against us[/spoiler]

VTC
Starting Member

9 Posts

Posted - 2011-10-10 : 21:30:02

Sure!

SELECT [person],[Apple],[Grapes],[Kiwi],[Pear],[Starfruit],[NotUsed1],[NotUsed2] FROM
(
SELECT ft.fooditem,p.person
FROM FoodTable ft
LEFT JOIN Person p
ON p.[fooditem] = ft.[fooditem]
) t
PIVOT (MAX(CASE WHEN p.foodItem IS NOT NULL THEN 'X' ELSE '' END) FOR ft.fooditem IN (],[Apple],[Grapes],[Kiwi],[Pear],[Starfruit],[NotUsed1],[NotUsed2]))

Also, I forgot to mention, I removed the p from the end of the query. I didn't see the reason for it because p is being set to the Person table earlier in the query. Let me know if it needs to be re-added.

Vigo the Carpathian

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 22:24:10

try this query instead


DECLARE @FoodList varchar(2000), 
	@ColList varchar(2000), 
	@Sql varchar(8000)

SELECT	@FoodList =	isnull(@FoodList + ',', '') + quotename(fooditem) 
FROM 	FoodTable

SELECT	@ColList =	isnull(@ColList + ',', '') + 'CASE WHEN ' + quotename(fooditem) + ' IS NOT NULL THEN ''X'' ELSE '''' END AS ' + quotename(fooditem) 
FROM 	FoodTable

SELECT	@Sql = 'SELECT person,' + @ColList + 
' FROM
(
	SELECT 	ft.fooditem, p.person
	FROM 	[FoodTable] ft
		LEFT JOIN Person p	ON 	p.[fooditem] = ft.[fooditem]
)t
PIVOT 
(
	MAX(fooditem) 
	FOR fooditem IN (' + @FoodList + ')
)p'

print @Sql
EXEC(@Sql)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 22:27:16

quote:
I removed the p from the end of the query. I didn't see the reason for it because p is being set to the Person table earlier in the query

The "p" at the end of the query is required. It is the table alias for the PIVOT table. It should not be confused with the "p" for the Person table. The alias "p" for the Person table is in the inner level, the last "p" is at the outer level. It is alright to use the same alias name / symbol as it is at different level

KH
[spoiler]Time is always against us[/spoiler]

VTC
Starting Member

9 Posts

Posted - 2011-10-10 : 22:52:50

AHHH!!!! THIS IS AMAZING!!! Khtan, Thank you SO MUCH! I'm going to spend the rest of the day taking apart every bit of that query so I understand fully how it works.

One last question and then I'm done - honest! There is a row returned that has NULL as the person and Xs for any food that isn't referenced by another person. What do I need to add to the query to remove that row such that I only have people from the Person table returned? Under normal circumstances, I understand you can just throw in a WHERE person IS NOT NULL, but I was just looking over the documentation for PIVOT and it doesn't look like WHERE can be used in queries with PIVOT (at least from the examples on the page). Enlighten me, please, oh great Khtan.

Thanks so much yet again!

VTC
Starting Member

9 Posts

Posted - 2011-10-10 : 22:58:58

quote:
The "p" at the end of the query is required. It is the table alias for the PIVOT table. It should not be confused with the "p" for the Person table. The alias "p" for the Person table is in the inner level, the last "p" is at the outer level. It is alright to use the same alias name / symbol as it is at different level

Ah, right. I understand now. I didn't realize you could use the same alias when used at different levels. I guess that makes sense, though; I'd just never thought too deeply on it. That's really good to know.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-10 : 23:50:30

quote:
Originally posted by VTC
There is a row returned that has NULL as the person and Xs for any food that isn't referenced by another person. What do I need to add to the query to remove that row such that I only have people from the Person table returned?

change from LEFT JOIN to INNER JOIN


FROM    [FoodTable] ft
	LEFTINNER JOIN Person p

KH
[spoiler]Time is always against us[/spoiler]

VTC
Starting Member

9 Posts

Posted - 2011-10-11 : 00:27:40

You are truly amazing. Know that when I get the query updated to use the real tables and am able to release the finished product into my office that you will be greatly and highly mentioned for providing the raw query magic needed to get this working. Thank you so much yet again. I'm only about 1/2 way through researching all the different parts of how the query goes together and this has already been a great learning experience.

Vigo the Carpathian

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-11 : 00:37:56

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources