Slelect Problem: kind of like a pivot but different

Question

Slelect Problem: kind of like a pivot but different

nsontag

SSC Veteran

Points: 211
More actions
May 17, 2010 at 2:05 pm

#221110

Hi,
I'm currently working with a table that stores several types of information on employees. The type of information stored in denoted in one column (data_type), whereas the actual data is stored in another column (alpha_value). As a result, this table has one record for each combination of employee and data type.
I'm trying to make a select statement that will produce one record for each employee with columns for a few specific values in the "data type" column of the original table. these columns would then be populated with the value corresponding to that employee and data type.
I'm essentially trying to do a pivot, but instead of aggregate data, I need to return a single database value that corresponds to the data type and employee
I'm hoping that sort of makes sense. If anyone can provide some advice on this, you're amazing.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I don't really understand what your table actually looks like.

Would you please post the table def, some sample data, your expected result and what you've tried so far? For details on how to do that please follow the first link in my signature.

Edit: wording changed.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nsontag SSC Veteran Points: 211 More actions · Answer 2

Fair enough:

Here is the SQL for a simplified version of the table I am working with. Note that the "field_code" and "person_id" fields would both be foreign keys in the actual table:

--===== Create the test table

CREATE TABLE empusertest (

person_id int NOT NULL,

field_code varchar(32),

alpha_value varchar(254),

PRIMARY KEY (person_id, field_code)

)

--===== Insert the test data into the test table

INSERT INTO empusertest (person_id, field_code, alpha_value)

SELECT 1,'CHARGEABILITY',NULL UNION ALL

SELECT 1,'LEVEL','SR1' UNION ALL

SELECT 1,'MENTOR','Williams, Mike' UNION ALL

SELECT 1,'TITLE','Subcontractor' UNION ALL

SELECT 2,'CHARGEABILITY',NULL UNION ALL

SELECT 2,'LEVEL','PRJ4' UNION ALL

SELECT 2,'MENTOR','Mayes, Mary' UNION ALL

SELECT 2,'TITLE','Data Specialist' UNION ALL

SELECT 3,'CHARGEABILITY',NULL UNION ALL

SELECT 3,'LEVEL','TE5' UNION ALL

SELECT 3,'MENTOR','Mayes, Mary' UNION ALL

SELECT 3,'TITLE','Environmental Technologist'

Here is an example of the results table I would like to produce:

--===== Create the sample results table

CREATE TABLE empuserresults (

person_id int PRIMARY KEY,

level_code varchar(32),

alpha_value varchar(254)

)

--===== Insert sample data into table

INSERT INTO empuserresults (person_id, level_code, alpha_value)

SELECT 1,'SR1','Subcontractor' UNION ALL

SELECT 2,'PRJ4','Data Specialist' UNION ALL

SELECT 3,'TE5','Environmental Technologist'

Thanks in advance!

LutzM SSC Guru Points: 107049 More actions · Answer 3

Here's a tested version based on your sample data. That's the advantage of providing ready to use sample data the way you did! Excellent job!!

Side note: the concept below is called "CrossTabQuery" and is described in the related link in my signature.

If you have a unknown number of values in field_code, you might want to have a look into the DynamicCrossTab article, also referenced in my signature. If you need to do it dynamic and you have trouble modify it to your needs post back here.

SELECT

person_id,

MAX(CASE WHEN field_code='LEVEL' THEN alpha_value ELSE NULL END) AS level_code,

MAX(CASE WHEN field_code='TITLE' THEN alpha_value ELSE NULL END) AS alpha_value

FROM empusertest

GROUP BY person_id

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nsontag SSC Veteran Points: 211 More actions · Answer 4

Interesting..so this is essentially creating a binary test for selected terms and then selecting the max values from those tests and grouping by employee ID.

That's great!

Thanks for all your help!

LutzM SSC Guru Points: 107049 More actions · Answer 5

nsontag (5/17/2010)
Interesting..so this is essentially creating a binary test for selected terms and then selecting the max values from those tests and grouping by employee ID.
That's great!
Thanks for all your help!

You're very welcome. But you've done most of the work yourself by providing the sample data in a ready to use format. When posting like that it's really easy for us to focus on the solution. So THANK YOU again!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 6

@nsontag,

Nice job. It was only your second post and you came right around with some really nice test data. A lot of people squawk about doing such a thing but when a pro like Lutz has a little something to work with, code just flies off their fingertips. 😉

Really nice job for a brand new newbie. 🙂

@Lutz... heh... what can I say... a quality job as usual.

--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)

LutzM SSC Guru Points: 107049 More actions · Answer 7

Jeff Moden (5/17/2010)
@nsontag,
Nice job. It was only your second post and you came right around with some really nice test data. A lot of people squawk about doing such a thing but when a pro like Lutz has a little something to work with, code just flies off their fingertips. 😉
Really nice job for a brand new newbie. 🙂
@Lutz... heh... what can I say... a quality job as usual.

:blush:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]