rows as columns matrix query or view

[LloydDobler]

Hi all, hoping someone can help with this and let me know if it's even possible: I have a table of training results, which needs to be displayed in a matrix, here's my table and desired output:

table A

EE TMOD LEVEL
1234 first aid 1
1111 first aid 1
9999 first aid 2
1234 safety 1
1111 safety 2
1234 basket weaving 1


to look like this:

EE first aid safety basket weaving
1234 1 1 1
1111 1 2 NULL
9999 2 NULL NULL




"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[ESquared]

I have defeated the 8000-character limit (I got to 80,000) with multiple sql variables, multiple passes against the information_schema, and some control variables to start and end at the right times, topped off with EXEC @SQL1 + @SQL2 + @SQL3 and so on, but it was somewhat convoluted as you can imagine. I'm not sure if you want to go there.

It was worth it to me to write this code because it was not for single use but is an oft-repeated procedure I use to automatically build dozens of views against source data.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[markgrizzle]

Just wanted to chime in. I was looking over my own problem with the 8000 limit, and found that select @sql didn't return the full string, but print @sql did (thanks again George). Not sure why.

Also, I handled the 8000 max varchar and exec() character limit with nested if statements in my dynamic sql while loop. Crude, but necessary in my case, since it's unlikely we're moving to sql 2005 anytime soon, and my corporate overlords want key metrics on a web page on the cheap.

Regards,
Mark

 

[gmmastros]

>> Just wanted to chime in. I was looking over my own problem with the 8000 limit, and found that select @sql didn't return the full string, but print @sql did (thanks again George). Not sure why.

I know why, and so will you after you read this.

When using Query Analyzer, you need to realize that it does nice things for you. It allows you to enter queries, and it shows you the results of the query. By default, QA only shows you the first 256 characters for each value.

In Query Analyzer, click Tools -> Options
Click the Results tab.
Notice the 'Maximum characters per column' is set to 256.

This is the default, but you can change the value if you like. Set it to 8000 and then you will notice that the entire @sql value will be displayed.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom