Displaying rows into cloumns using sql-server 2000

Question

Displaying rows into cloumns using sql-server 2000

surya prakash

SSC-Addicted

Points: 455
More actions
December 1, 2006 at 12:02 pm

#121687

I am havinng 3 tables which as shown below
---------------------------------------------------------
Create Table tbl_Employee( Empid int,
EmpName varchar(100),
City    varchar(100))
Insert into tbl_Employee values (1001,'David','kkkk')
Insert into tbl_Employee values (1001,'Rigge','fffff')
Create Table tbl_Skill( Skill_ID int,
skill_name varchar(100),
Skillgrp varchar(100))
Insert into tbl_Skill values (1,'EJB','Java')
Insert into tbl_Skill values (2,'CORBA','Java')
Insert into tbl_Skill values (3,'Struts','Java')
Insert into tbl_Skill values (4,'RMI','Java')
Insert into tbl_Skill values (5,'BizTalk','MST')
Insert into tbl_Skill values (6,'VBScript','MST')
Insert into tbl_Skill values (7,'ASP.Net','MST')

create table tbl_Empskill( skill_id int,
Empid int, Experience int)
insert into tbl_Empskill values (1,1001,2)
insert into tbl_Empskill values (2,1001,3)
insert into tbl_Empskill values (3,1001,4)
insert into tbl_Empskill values (4,1001,6)
insert into tbl_Empskill values (5,1002,2)
insert into tbl_Empskill values (6,1002,3)
insert into tbl_Empskill values (7,1002,1)
insert into tbl_Empskill values (2,1002,4)
-------------------------------------------------------------------
I m looking for the out put like this
Employeeid|EmpName | EJB |CORBA|Struts|RMI |BizTalk|VBScript|ASP.Net|..................
1001      | David | 2 |3    |4     |6    | NULL|NULL|NULL
1002      | Rigge |NULL |4    |NULL |NULL | 2   |3|1
The cirictical part here I need to display All skills which are exisisting in the tbl_Skill tables as a header part
and matching employees expierence into columns matching to the skill set.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phillip Koebbe-358294 SSC Journeyman Points: 79 More actions · Answer 1

The simple answer is you have to create a temp table dynamically based on the columns you want to pivot out. Then you have to populate that temp table with the data, and select the temp table back to the caller.

The more complex answer is how you actually do that. Basically you have to query the INFORMATION_SCHEMA views for the columns in the tables you want to pivot and create your temp table based on those results. Populating the temp table will work essentially the same way.

We needed to do that for reporting but it was painfully slow when more the whole population of entities needed to be returned. We ended up creating a pivot table that gets populated as the attributes get created. Then the report pulls data from the pivot table and it's much faster.

Peace,

Phillip

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Uhp... nope... don't need a temp table for this one, Phillip... and we don't need to go near INFORMATION_SCHEMA or SysObjects... and it flies even with lot's of data.

Suryah,

Thank you very much for posting sample data... made my job a heck of a lot easier. You do have one error in the sample data for tbl_Employee... both employees have an EmpID of 1001. I changed that for my testing.

Speaking of testing, this is the code you want... it works against the test data you provided.... For best results, run it in the Grid Mode of Query Analyzer. The dynamic SQL that gets executed will show up in the Messages tab...

--===== Declare some variables to hold dynamic SQL in

DECLARE @SQLHeader VARCHAR(8000)

DECLARE @SQLBody VARCHAR(8000)

DECLARE @SQLFooter VARCHAR(8000)

--===== Create the first static part of the dynamic SQL

SELECT @SQLHeader = ' SELECT e.EmpID,e.EmpName,' + CHAR(13)

--===== Create the truly dynamic part of the SQL based on columns in tbl_Skill

SELECT @SQLBody = ISNULL(@SQLBody + ','+CHAR(13),'')

+ ' MAX(CASE WHEN es.Skill_ID = '+STR(s.Skill_ID,3)

+ ' THEN es.Experience ELSE NULL END) AS [' + s.Skill_Name + ']'

FROM tbl_Skill s

ORDER BY s.Skill_ID

--===== Create the last part of the dynamic SQL which is also static

SELECT @SQLFooter = CHAR(13)

+ ' FROM tbl_Employee e' + CHAR(13)

+ ' LEFT OUTER JOIN tbl_EmpSkill es' + CHAR(13)

+ ' ON e.EmpID = es.EmpID' + CHAR(13)

+ ' INNER JOIN tbl_Skill s' + CHAR(13)

+ ' ON es.Skill_ID = s.Skill_ID' + CHAR(13)

+ ' GROUP BY e.EmpID,e.EmpName' + CHAR(13)

--===== Display the dynamic SQL we are about to execute

-- (May be commented out)

PRINT @SQLHeader + @SQLBody + @SQLFooter

--===== Execute the dynamic SQL to get the desired result set

EXEC (@SQLHeader + @SQLBody + @SQLFooter)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phillip Koebbe-358294 SSC Journeyman Points: 79 More actions · Answer 3

Hey Jeff,

You're right. Don't need a temp table and all the complexity for a fixed set of fields. I overlooked that part of his dilemma. Our situation is different, though. We have a system in which the user can define what we call a "characteristic", and it can be anything imaginable: color, weight, length, blah blah blah. I was basing my response on that and just wasn't paying enough attention.

Thanks for giving Suryah a simpler, more accurate solution to his problem.

Peace,

Phillip

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Heh... thanks for the feedback, Phillip... I kinda figured it was something like that. Every once in a while, I'll look at one of these posts, start writing code, go back to the post to verify something and dang if I didn't read the whole thing wrong. It happens.

I am very interested in your system for user defined "characteristics". Can you tell me more about that without giving up anything proprietary? If it's what I think it is, I may be able to help a bit there. If it's not what I think it is, I'd still like to learn a bit more about it... sounds useful and maybe even fun. Feel free to private email me if you don't want to broadcast too much...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phillip Koebbe-358294 SSC Journeyman Points: 79 More actions · Answer 5

Hi Jeff,

I'd be happy to share some information, but it might be a few days before I can find the time to sit down and think through it well enough to be coherent. I didn't write this system, and I've been on board for only about 3.5 months, so I'm still stumbling around a bit here and there. And thanks for the sensitivity to proprietary information.

Peace,

Phillip

surya prakash SSC-Addicted Points: 455 More actions · Answer 6

Thanks for writing the query for me Philip.

I tried to write but I couldn't figure out how to write that type of query.

You are really having creative ideas writing in SQL quries.

Thanks to Jeff also giving valuable suggestions.

Surya

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Actually, Philip didn't write the query... I did...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

surya prakash SSC-Addicted Points: 455 More actions · Answer 8

surya prakash

SSC-Addicted

Points: 455

December 5, 2006 at 5:44 am

#675780

Sorry Jeff.

Thanks once again Jeff.

surya