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."

 

[gmmastros]

Something like this???

[COLOR=blue]Declare[/color] @TableA [COLOR=blue]Table[/color](EE [COLOR=blue]Int[/color], TMOD [COLOR=blue]VarChar[/color](30), [COLOR=#FF00FF]Level[/color] [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](1234,[COLOR=red]'first aid'[/color]     ,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](1111,[COLOR=red]'first aid'[/color]     ,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](9999,[COLOR=red]'first aid'[/color]     ,2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](1234,[COLOR=red]'safety'[/color]        ,1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](1111,[COLOR=red]'safety'[/color]        ,2)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @TableA [COLOR=blue]Values[/color](1234,[COLOR=red]'basket weaving'[/color],1)

[COLOR=blue]Select[/color] EE, 
       [COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] TMOD=[COLOR=red]'first aid'[/color] [COLOR=blue]Then[/color] [COLOR=#FF00FF]Level[/color] [COLOR=blue]End[/color]) [COLOR=blue]As[/color] [[COLOR=blue]First[/color] Aid],
       [COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] TMOD=[COLOR=red]'safety'[/color] [COLOR=blue]Then[/color] [COLOR=#FF00FF]Level[/color] [COLOR=blue]End[/color]) [COLOR=blue]As[/color] [safety],
       [COLOR=#FF00FF]Min[/color]([COLOR=blue]Case[/color] [COLOR=blue]When[/color] TMOD=[COLOR=red]'basket weaving'[/color] [COLOR=blue]Then[/color] [COLOR=#FF00FF]Level[/color] [COLOR=blue]End[/color]) [COLOR=blue]As[/color] [basket weaving]
[COLOR=blue]From[/color]   @TableA
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] EE

-George

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

 

[LloydDobler]

Sorry, I guess I failed to mention this; I don't want to hard code any of the TMOD values, as they can change. Some can be added and some removed.

"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."

 

[gmmastros]

If you are using SQL2000, then I recommend dynamic SQL with the code I show above as a basis.

If you are using SQL2005, then do a little research on the PIVOT command.



-George

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

 

[LloydDobler]

sorry, It's 2000, but you'll have to explain 'dynamic SQL'. I'm not a newbie, but I'm not exactly a guru either.

"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."

 

[gmmastros]

Do a google search for [google]Dynamic SQL[/google].

Essentially, it's when you dynamically create a query that will be run.

Here's an example....

Create Table #TableA(EE Int, TMOD VarChar(30), Level int)

Insert Into #TableA Values(1234,'first aid'     ,1)
Insert Into #TableA Values(1111,'first aid'     ,1)
Insert Into #TableA Values(9999,'first aid'     ,2)
Insert Into #TableA Values(1234,'safety'        ,1)
Insert Into #TableA Values(1111,'safety'        ,2)
Insert Into #TableA Values(1234,'basket weaving',1)
Insert Into #TableA Values(1234,'Blah',1)
Insert Into #TableA Values(1234,'Foo',1)

Declare @sql VarChar(8000)
Set @sql = ''

Select @sql = @sql + Data
From   (
       Select Distinct 'Min(Case When TMOD=''' + TMOD + ''' Then Level End) As [' + TMOD + '],' As Data
       From   #TableA) As A

If Right(@sql, 1) = ','
	Set @sql = Left(@sql, Len(@sql)-1)

Set @sql = 'Select EE, ' + @sql + ' From #TableA Group By EE'
Select @sql

Exec (@sql)

Drop Table #TableA

-George

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

 

[LloydDobler]

The output works, but how do I do this with data that already exists in TableA?

I tried scraping the code from Declare @sql to Exec @sql but it errors out with:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'From'.

Declare @sql VarChar(8000)
Set @sql = ''

Select @sql = @sql + Data
From   (
       Select Distinct 'Min(Case When Trainingmodule=''' + Trainingmodule + ''' Then Levelcomplete End) As [' + Trainingmodule + '],' As Data
       From   trainingtable) As A

If Right(@sql, 1) = ','
    Set @sql = Left(@sql, Len(@sql)-1)

Set @sql = 'Select Employeenumber, ' + @sql + ' From trainingtable Group By Employeenumber'
Select @sql

Exec (@sql)

"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."

 

[gmmastros]

I don't see anything obvious. Can you post the results of the 'Select @sql' line? That will probably help to narrow down the problem.



-George

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

 

[LloydDobler]

sorry george, not following ya? How can I post results of 'select @sql' line? I'm not getting anything back in Query Analyzer except:

"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'From'."

"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."

 

[gmmastros]

Comment out this... Exec (@sql)

Then, in the output window, you should see the query that is created. You may have some difficulty displaying all of it (by default Query Analyzer only shows the first ?255? characters of the output). You can fix this by using 'results in text'. To turn this on, press CTRL-T before running the query. To switch back to 'results in grid', press CTRL-D.





-George

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

 

[LloydDobler]

output is as follows:

Select Employeenumber, Min(Case When Trainingmodule='Group Leader Training' Then Levelcomplete End) As [Group Leader Training],Min(Case When Trainingmodule='Forklift Training Practical' Then Levelcomplete End) As [Forklift Training Practical],Min(Case When

(1 row(s) affected)

"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."

 

[gmmastros]

That's not all of it. Press CTRL-T and re-run it.



-George

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

 

[LloydDobler]

that's all i get, stops at that last line "(case when". I tried results to file and same thing.

"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."

 

[gmmastros]

try changing 'Select @sql' to:

Print @sql

When you do this, there will be a messages tab at the bottom of the window.

-George

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

 

[LloydDobler]

I think that did it, but didn't see the messages tab. here's what I got:

Select Employeenumber, Min(Case When Trainingmodule='Group Leader Training' Then Levelcomplete End) As [Group Leader Training],Min(Case When Trainingmodule='Forklift Training Practical' Then Levelcomplete End) As [Forklift Training Practical],Min(Case When Trainingmodule='Shipping Training' Then Levelcomplete End) As [Shipping Training],Min(Case When Trainingmodule='Millwright Certificate of Qualification' Then Levelcomplete End) As [Millwright Certificate of Qualification],Min(Case When Trainingmodule='microprocessor applications' Then Levelcomplete End) As [microprocessor applications],Min(Case When Trainingmodule='Excel Business Applications' Then Levelcomplete End) As [Excel Business Applications],Min(Case When Trainingmodule='Operating Procedures' Then Levelcomplete End) As [Operating Procedures],Min(Case When Trainingmodule='Suprastuff' Then Levelcomplete End) As [Suprastuff],Min(Case When Trainingmodule='Quick Connect' Then Levelcomplete End) As [Quick Connect],Min(Case When Trainingmodule='JCI' Then Levelcomplete End) As [JCI],Min(Case When Trainingmodule='Train the Trainer' Then Levelcomplete End) As [Train the Trainer],Min(Case When Trainingmodule='Introduction to Capacity Database' Then Levelcomplete End) As [Introduction to Capacity Database],Min(Case When Trainingmodule='Crane Training' Then Levelcomplete End) As [Crane Training],Min(Case When Trainingmodule='Work Instructions' Then Levelcomplete End) As [Work Instructions],Min(Case When Trainingmodule='Principles of Human Resources' Then Levelcomplete End) As [Principles of Human Resources],Min(Case When Trainingmodule='Human Relations' Then Levelcomplete End) As [Human Relations],Min(Case When Trainingmodule='Job Specific Training' Then Levelcomplete End) As [Job Specific Training],Min(Case When Trainingmodule='Concepts of Reliability' Then Levelcomplete End) As [Concepts of Reliability],Min(Case When Trainingmodule='Enabling Device' Then Levelcomplete End) As [Enabling Device],Min(Case When Trainingmodule='Product Knowledge' Then Levelcomplete End) As [Product Knowledge],Min(Case When Trainingmodule='SPC Level 2.1' Then Levelcomplete End) As [SPC Level 2.1],Min(Case When Trainingmodule='Communication Concepts' Then Levelcomplete End) As [Communication Concepts],Min(Case When Trainingmodule='New Employee Orientation Including Operating Policy, Health and Safety, Right to Refuse' Then Levelcomplete End) As [New Employee Orientation Including Operating Policy, Health and Safety, Right to Refuse],Min(Case When Trainingmodule='ISO 14001- Aspects and Impacts' Then Levelcomplete End) As [ISO 14001- Aspects and Impacts],Min(Case When Trainingmodule='Measurement System Analysis' Then Levelcomplete End) As [Measurement System Analysis],Min(Case When Trainingmodule='Fundamentals of Purchasing' Then Levelcomplete End) As [Fundamentals of Purchasing],Min(Case When Trainingmodule='DR Deburr' Then Levelcomplete End) As [DR Deburr],Min(Case When Trainingmodule='Introduction to Electropneumatics' Then Levelcomplete End) As [Introduction to Electropneumatics],Min(Case When Trainingmodule='Electrician-Construction and Maintenance' Then Levelcomplete End) As [Electrician-Construction and Maintenance],Min(Case When Trainingmodule='Crane Training Practical' Then Levelcomplete End) As [Crane Training Practical],Min(Case When Trainingmodule='Endformer' Then Levelcomplete End) As [Endformer],Min(Case When Trainingmodule='EN114' Then Levelcomplete End) As [EN114],Min(Case When Trainingmodule='Design of Experiments - Basics' Then Levelcomplete End) As [Design of Experiments - Basics],Min(Case When Trainingmodule='DR-2002' Then Levelcomplete End) As [DR-2002],Min(Case When Trainingmodule='Industrial Electronics' Then Levelcomplete End) As [Industrial Electronics],Min(Case When Trainingmodule='Digital Electronics 2' Then Levelcomplete End) As [Digital Electronics 2],Min(Case When Trainingmodule='Valeo Inspection' Then Levelcomplete End) As [Valeo Inspection],Min(Case When Trainingmodule='Problem Solving' Then Le From trainingtable Group By Employeenumber


"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."

 

[gmmastros]

I have an idea.... Please run the following in Query Analyzer and post the results here.

[tt][blue]
Select Data_Type, Character_Maximum_Length
From Information_Schema.Columns
Where Column_Name = 'Trainingmodule'
And Table_Name = 'trainingtable'
[/blue][tt]

-George

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

 

[LloydDobler]

nvarchar 150

"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."

 

[gmmastros]

ok. Here's what I think is going on.

In SQL Server 2000, the maximum length of a string variable is 8000 bytes. varchar requires 1 byte per character. nvarchar uses 2 bytes per character (to accomodate unicode data). I think there is an implicit conversion happening here, which is not allowing your string to get larger than 4000 characters. I would suggest that you convert your data to varchar within the query. Like this...

Declare @sql VarChar(8000)
Set @sql = ''

Select @sql = @sql + Data
From   (
       Select Distinct 'Min(Case When Trainingmodule=''' + [!]Convert(varchar(150), [/!]Trainingmodule[!])[/!] + ''' Then Levelcomplete End) As [' + [!]Convert(varchar(150), [/!]Trainingmodule[!])[/!] + '],' As Data
       From   trainingtable) As A

If Right(@sql, 1) = ','
    Set @sql = Left(@sql, Len(@sql)-1)

Set @sql = 'Select Employeenumber, ' + @sql + ' From trainingtable Group By Employeenumber'
Select @sql

Exec (@sql)

Cross your fingers and try it now.

-George

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

 

[LloydDobler]

Errors out again:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Level'.

Select Employeenumber, Min(Case When Trainingmodule='Industrial Mechanic Millwright Apprenticeship' Then Levelcomplete End) As [Industrial Mechanic Millwright Apprenticeship],Min(Case When Trainingmodule='Master Planning of Resources' Then Levelcomplete End) As [Master Planning of Resources],Min(Case When Trainingmodule='Industrial Mechanic Millwright Certification' Then Levelcomplete End) As [Industrial Mechanic Millwright Certification],Min(Case When Trainingmodule='Introduction to Adult Learners' Then Levelcomplete End) As [Introduction to Adult Learners],Min(Case When Trainingmodule='Employee Relations' Then Levelcomplete End) As [Employee Relations],Min(Case When Trainingmodule='Press Fundamentals' Then Levelcomplete End) As [Press Fundamentals],Min(Case When Trainingmodule='Capability' Then Levelcomplete End) As [Capability],Min(Case When Trainingmodule='Accident Reporting' Then Levelcomplete End) As [Accident Reporting],Min(Case When Trainingmodule='Fundamentals of Project Management' Then Levelcomplete End) As [Fundamentals of Project Management],Min(Case When Trainingmodule='Corrective Action Database' Then Levelcomplete End) As [Corrective Action Database],Min(Case When Trainingmodule='Project Introduction, Planning and Control' Then Levelcomplete End) As [Project Introduction, Planning and Control],Min(Case When Trainingmodule='Excel 2000 Level 3' Then Levelcomplete End) As [Excel 2000 Level 3],Min(Case When Trainingmodule='Privacy in the Workplace' Then Levelcomplete End) As [Privacy in the Workplace],Min(Case When Trainingmodule='Material Handling And Management Concepts ' Then Levelcomplete End) As [Material Handling And Management Concepts ],Min(Case When Trainingmodule='Microsoft Access 2000 Intermediate' Then Levelcomplete End) As [Microsoft Access 2000 Intermediate],Min(Case When Trainingmodule='Die Design' Then Levelcomplete End) As [Die Design],Min(Case When Trainingmodule='Quality Management Systems Lead Auditor Certificat' Then Levelcomplete End) As [Quality Management Systems Lead Auditor Certificat],Min(Case When Trainingmodule='DR Gauging' Then Levelcomplete End) As [DR Gauging],Min(Case When Trainingmodule='DR' Then Levelcomplete End) As [DR],Min(Case When Trainingmodule='welding metallurgy' Then Levelcomplete End) As [welding metallurgy],Min(Case When Trainingmodule='Introduction to Pneumatics' Then Levelcomplete End) As [Introduction to Pneumatics],Min(Case When Trainingmodule='Introduction to Hydraulics' Then Levelcomplete End) As [Introduction to Hydraulics],Min(Case When Trainingmodule='EN Gauging' Then Levelcomplete End) As [EN Gauging],Min(Case When Trainingmodule='Suprastuff' Then Levelcomplete End) As [Suprastuff],Min(Case When Trainingmodule='windows xp' Then Levelcomplete End) As [windows xp],Min(Case When Trainingmodule='Machine Specific TrainingMachine Specific Training' Then Levelcomplete End) As [Machine Specific TrainingMachine Specific Training],Min(Case When Trainingmodule='Lockout Troubleshooting 97-68 Quad' Then Levelcomplete End) As [Lockout Troubleshooting 97-68 Quad],Min(Case When Trainingmodule='Trade Calculations' Then Levelcomplete End) As [Trade Calculations],Min(Case When Trainingmodule='Power Transmission' Then Levelcomplete End) As [Power Transmission],Min(Case When Trainingmodule='Microsoft Access 2000 Introdcution' Then Levelcomplete End) As [Microsoft Access 2000 Introdcution],Min(Case When Trainingmodule='Supervisor' Then Levelcomplete End) As [Supervisor],Min(Case When Trainingmodule='Engineering Drawing and Blue Print Reading' Then Levelcomplete End) As [Engineering Drawing and Blue Print Reading],Min(Case When Trainingmodule='lean manufacturing' Then Levelcomplete End) As [lean manufacturing],Min(Case When Trainingmodule='Lift Truck Safety Awareness Course' Then Levelcomplete End) As [Lift Truck Safety Awareness Course],Min(Case When Trainingmodule='Fundamentals of Purchasing' Then Levelcomplete End) As [Fundamentals of Purchasing],Min(Case When Trainingmodule='Six Sigma Detailed Studies' Then Levelcomplete End) As [Six Sigma Detailed Studies],Min(Case When Trainingmodule='ISO 14001 Registered Lead Auditor Training' Then Levelcomplete End) As [ISO 14001 Registered Lead Auditor Training],Min(Case When Trainingmodule='Material Handling Training' Then Levelcomplete End) As [Material Handling Training],Min(Case When Trainingmodule='Harassment Level I' Then Levelcomplete End) As [Harassment Level I],Min(Case When Trainingmodule='QS 9000 Procedures' Then Levelcomplete End) As [QS 9000 Procedures],Min(Case When Trainingmodule='Receiving Training' Then Levelcomplete End) As [Receiving Training],Min(Case When Trainingmodule='Microsoft Project 2000 Introduction' Then Levelcomplete End) As [Microsoft Project 2000 Introduction],Min(Case When Trainingmodule='Machine Tool Setter Operator' Then Levelcomplete End) As [Machine Tool Setter Operator],Min(Case When Trainingmodule='ISO 14001 Internal Auditing' Then Levelcomplete End) As [ISO 14001 Internal Auditing],Min(Case When Trainingmodule='PPAP' Then Levelcomplete End) As [PPAP],Min(Case When Trainingmodule='SPC Level 2.1' Then Levelcomplete End) As [SPC Level 2.1],Min(Case When Trainingmodule='Introduction to Business Management' Then Levelcomplete End) As [Introduction to Business Management],Min(Case When Trainingmodule='Quick Connect' Then Levelcomplete End) As [Quick Connect],Min(Case When Trainingmodule='Metrology' Then Levelcomplete End) As [Metrology],Min(Case When Trainingmodule='Industrial Ergonomics' Then Levelcomplete End) As [Industrial Ergonomics],Min(Case When Trainingmodule='Scrap' Then Levelcomplete End) As [Scrap],Min(Case When Trainingmodule='Other' Then Levelcomplete End) As [Other],Min(Case When Trainingmodule='Sky Jack' Then Levelcomplete End) As [Sky Jack],Min(Case When Trainingmodule='Statistical Process Control 2' Then Levelcomplete End) As [Statistical Process Control 2],Min(Case When Trainingmodule='Endformer' Then Levelcomplete End) As [Endformer],Min(Case When Trainingmodule='MiiC' Then Levelcomplete End) As [MiiC],Min(Case When Trainingmodule='supravision' Then Levelcomplete End) As [supravision],Min(Case When Trainingmodule='Payroll Manager' Then Levelcomplete End) As [Payroll Manager],Min(Case When Trainingmodule='Gauging- Measuring Methods Overview' Then Levelcomplete End) As [Gauging- Measuring Methods Overview],Min(Case When Trainingmodule='Lockout Theory' Then Levelcomplete End) As [Lockout Theory],Min(Case When Trainingmodule='Web RFQ for Production Buyers ' Then Levelcomplete End) As [Web RFQ for Production Buyers ],Min(Case When Trainingmodule='Health, Safety & The Law' Then Levelcomplete End) As [Health, Safety & The Law],Min(Case When Trainingmodule='Front Desk Superstar' Then Levelcomplete End) As [Front Desk Superstar],Min(Case When Trainingmodule='Basic Certification Level I' Then Levelcomplete End) As [Basic Certification Level I],Min(Case When Trainingmodule='EN114' Then Levelcomplete End) As [EN114],Min(Case When Trainingmodule='Superboost Your Memory' Then Levelcomplete End) As [Superboost Your Memory],Min(Case When Trainingmodule='Detailed Planning and Scheduling' Then Levelcomplete End) As [Detailed Planning and Scheduling],Min(Case When Trainingmodule='Troubleshooting ' Then Levelcomplete End) As [Troubleshooting ],Min(Case When Trainingmodule='Priority/Time Management' Then Levelcomplete End) As [Priority/Time Management],Min(Case When Trainingmodule='JHSC Basic Certification Training' Then Levelcomplete End) As [JHSC Basic Certification Training],Min(Case When Trainingmodule='Harassment Level II' Then Levelcomplete End) As [Harassment Level II],Min(Case When Trainingmodule='DR Deburr' Then Levelcomplete End) As [DR Deburr],Min(Case When Trainingmodule='Forklift Training Practical' Then Levelcomplete End) As [Forklift Training Practical],Min(Case When Trainingmodule='Operating Procedures' Then Levelcomplete End) As [Operating Procedures],Min(Case When Trainingmodule='Effective Negotiating' Then Level

"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."

 

[gmmastros]

You completely filled up your 8000 characters, so you cannot add any more. Given this... I would suggest that you start over. Just return the data as it exists in the table and format the output in your front end to accommodate this.

-George

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