Merging data from rows to column on duplicated ID

Question

Post reply

Merging data from rows to column on duplicated ID

Terri .K Wei

SSC Enthusiast

Points: 147
More actions
October 11, 2007 at 9:25 pm

#180142

I am currently working to solve this problem that i am facing.

I just cant' get my desired solution to my problem.

As per below is the current db view.

emp_id skill level years remarks

2541 COMPUTER BAIK 1

2541 WORD PROCESSING BAIK 1

2541 EXCEL BAIK 1

2541 POWERPOINT BAIK 1

how do i get this repeated emp_id to have a view of..

emp_id skill level

2541 COMPUTER.wordprocessin,excel,powerpoint BAIK,baik,baik,baik

years remarks

1,1,1,1

i just can't seem to get this comin up..

i tried the maxfunction.. seems to be givin the max result

SELECT COUNT(UV_EMPLOYEE_ID) AS Expr1, MAX(UV_SKILL) AS uv_skill, UV_EMPLOYEE_ID

FROM ORC_TB_SKILLS

GROUP BY UV_EMPLOYEE_ID

result:

3 WORD 64

3 WORD PROCESSING 579

which means that there are other two skills which is not shown.. i need to actually show all skill on duplicate

to uv_skill column...

sample output: 1234 excel,programming *assumingly 1234 is duplicated twice

pls advise.. yer help are most appreciated

regards,

k

Viewing 15 posts - 1 through 14 (of 14 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

ChiragNS

One Orange Chip

Points: 26137

October 12, 2007 at 12:02 am

#741442

Hi

See if this helps you.

declare @empskill VARCHAR(100)

set @empskill = ''

SELECT @empskill = @empskill + Skill level from table

where emp_id = 2541

select distinct emp_id,@empskill from table

where emp_id = 2541

"Keep Trying"

Terri .K Wei SSC Enthusiast Points: 147 More actions · Answer 2

hey Craig,

thanks for the reply to my query..

i guess it is working..

this is the code i m looking at rite now..

DECLARE @empskill2 VARCHAR(100)

SET @empskill2 = ''

SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks

FROM orc_tb_skills

WHERE uv_employee_id = 2541

SELECT DISTINCT uv_employee_id, @empskill2

FROM orc_tb_skills

WHERE uv_employee_id = 2541

question?

1) After running this script.. i have a pop up that say 1 row effected

n there isnt any output shown like the other normal query.. is this the case?

2) IF i would like to check for duplicated id's with the code below.. how do i go about checking for duplicate ----> merging duplicate...

here is my script for duplicating.. should i use the if else case?

please advise..

select uv_employee_id,count(uv_employee_id) as numofoccu

from orc_tb_skills

where count(uv_employee_id)>1

thank you for your time sir..

regards,

k

Terri .K Wei SSC Enthusiast Points: 147 More actions · Answer 3

the error message after executing query below is .. This designer does not graphically support the DECLARE CURSOR SQL construct. Is it becoz i m using sql2000?

DECLARE @empskill2 VARCHAR(100)

SET @empskill2 = ''

SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks

FROM orc_tb_skills

WHERE uv_employee_id = 2541

SELECT DISTINCT uv_employee_id, @empskill2

FROM orc_tb_skills

WHERE uv_employee_id = 2541

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

kokwei_w (10/17/2007)

hey Craig,

thanks for the reply to my query..

i guess it is working..

this is the code i m looking at rite now..

DECLARE @empskill2 VARCHAR(100)

SET @empskill2 = ''

SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks

FROM orc_tb_skills

WHERE uv_employee_id = 2541

SELECT DISTINCT uv_employee_id, @empskill2

FROM orc_tb_skills

WHERE uv_employee_id = 2541

question?

1) After running this script.. i have a pop up that say 1 row effected

n there isnt any output shown like the other normal query.. is this the case?

2) IF i would like to check for duplicated id's with the code below.. how do i go about checking for duplicate ----> merging duplicate...

here is my script for duplicating.. should i use the if else case?

please advise..

select uv_employee_id,count(uv_employee_id) as numofoccu

from orc_tb_skills

where count(uv_employee_id)>1

thank you for your time sir..

regards,

k

Hi

Sorry for the delay in replying.

Regarding your questions

1) To display the results you will have to run a select on the variable (@empskill2). This has to be done just after the statement that concatenates different values i.e. And both the select statements must be in a single batch. If you can put this in a stored proc it would be better.

SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks......

select uv_employee_id, @empskill2 from orc_tb_skills where uv_employee_id = 2541.

2) query for getting duplicate id's is correct.

"Keep Trying"

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

kokwei_w (10/17/2007)

the error message after executing query below is .. This designer does not graphically support the DECLARE CURSOR SQL construct. Is it becoz i m using sql2000?

DECLARE @empskill2 VARCHAR(100)

SET @empskill2 = ''

SELECT @empskill2 = @empskill2 + uv_level + uv_year + uv_remarks

FROM orc_tb_skills

WHERE uv_employee_id = 2541

SELECT DISTINCT uv_employee_id, @empskill2

FROM orc_tb_skills

WHERE uv_employee_id = 2541

Hi

Where are you doing this . I tried something similar in SQL 2000 QueryAnalyzer it was working. pls give more details.

"Keep Trying"

Terri .K Wei SSC Enthusiast Points: 147 More actions · Answer 6

dear chirag,

thank u sir.. this works fine now.. minor alterations here and there got this script working

i have modified n reused the codes to group the concatenation as such

DECLARE @empskill2 VARCHAR(200)

SET @empskill2 = ''

DECLARE @empskill3 VARCHAR(100)

SET @empskill3 = ''

DECLARE @empskill4 VARCHAR(100)

SET @empskill4 = ''

DECLARE @empskill5 VARCHAR(100)

SET @empskill5 = ''

SELECT @empskill2 = uv_skill + @empskill2, @empskill3 = uv_level + @empskill3 ,@empskill4 = uv_year + @empskill4,@empskill5 = uv_remarks + @empskill5

FROM orc_tb_skills

where uv_employee_id='2541'

SELECT distinct uv_employee_id, @empskill2 as 'Skills' ,@empskill3 as 'level',@empskill4 as 'year',@empskill5 as 'remarks'

FROM orc_tb_skills

where uv_employee_id='2541'

Question

1) it works fine for id=2541 but what if there is more then one duplicated id throughout the whole table of say est 3000 records.. i tried using the "and' "or" even "," to try getting the where clause to work.. but it is not giving me the desired output.. how do i go about this.. any suggestion.. ? Is it possible to do sumthin like this

e.g

where uv_employee_id='2541','1110','6666','1234','4567' to just concet all the duplicated id's from my duplication check script?

thanks for all yer replies ...looking forward to hear from you guru

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

kokwei_w (10/19/2007)

dear chirag,

thank u sir.. this works fine now.. minor alterations here and there got this script working

i have modified n reused the codes to group the concatenation as such

DECLARE @empskill2 VARCHAR(200)

SET @empskill2 = ''

DECLARE @empskill3 VARCHAR(100)

SET @empskill3 = ''

DECLARE @empskill4 VARCHAR(100)

SET @empskill4 = ''

DECLARE @empskill5 VARCHAR(100)

SET @empskill5 = ''

SELECT @empskill2 = uv_skill + @empskill2, @empskill3 = uv_level + @empskill3 ,@empskill4 = uv_year + @empskill4,@empskill5 = uv_remarks + @empskill5

FROM orc_tb_skills

where uv_employee_id='2541'

SELECT distinct uv_employee_id, @empskill2 as 'Skills' ,@empskill3 as 'level',@empskill4 as 'year',@empskill5 as 'remarks'

FROM orc_tb_skills

where uv_employee_id='2541'

Question

1) it works fine for id=2541 but what if there is more then one duplicated id throughout the whole table of say est 3000 records.. i tried using the "and' "or" even "," to try getting the where clause to work.. but it is not giving me the desired output.. how do i go about this.. any suggestion.. ? Is it possible to do sumthin like this

e.g

where uv_employee_id='2541','1110','6666','1234','4567' to just concet all the duplicated id's from my duplication check script?

thanks for all yer replies ...looking forward to hear from you guru

Hi

Plz dont call me sir.

Do you want to run this query for multiple userids.

If so i suggest you put the query in a stored procedure.

Inside the procedure you can have a "while" loop.

Inside the loop the query can be executed for all userids ONE BY ONE. Build a temp table with the results of each execution of the query. All this is inside the loop.

BTW will be out for the week end .. can answer back only on monday...:)

Regards

"Keep Trying"

Terri .K Wei SSC Enthusiast Points: 147 More actions · Answer 8

Dear chirag..

for the while... how do i determine the last record for employee_id

while uv_employee_id = .....

1) does sql 2000 has a function to determine the end of record set?

ooh i have no idea where to go about to start or do a stored procedure to run the query :unsure:

show me some light chirag

been workin n looking the whole week i just cant get any of them...

regards,

k

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

Hi K

I suggest you undertake some training on basics of SQL . Traning could be done within the organization you work in ... somebody senior could be your mentor or you can do some course in SQL.

Procedures are one of the most basic things in SQL and teaching how to write them thru this forum may not be the best way to go about it.

You can start off by looking into SQL server help. It has the syntax and also examples.

I could write the procedure that you want but what will you do when some new requirement comes up. Also if you may need to modify the procedure.

"Keep Trying"

Terri .K Wei SSC Enthusiast Points: 147 More actions · Answer 10

dear chirag,

i totally agree on your comments.. unfortunately there ain't anyone around to guide me where everyone is just being so busy with their owns.. and i have to seek for external guidance..

i will do alittle research on the stored procedure and i shall get back to you soon .. i guess i somehow already understand the concept of stored procedure..where as just a few questions i need to ask you chirag:

1)insert into statement after the query is done is to be placed after the codes or before the codes?

2)for the while statement.. i assume that we will be running it on uv_employee_id but how do we define the end of recordset?

I will get back to you in regards to this stored procedure if i face any difficulities...

thank you for yer time cirag hope to hear from you soon

good day,

k

Terri .K Wei SSC Enthusiast Points: 147 More actions · Answer 11

CREATE PROCEDURE bpm.kelulusandup as

DECLARE @empskill2 VARCHAR(200)

SET @empskill2 = ''

DECLARE @empskill3 VARCHAR(100)

SET @empskill3 = ''

DECLARE @empskill4 VARCHAR(100)

SET @empskill4 = ''

DECLARE @empskill5 VARCHAR(100)

SET @empskill5 = ''

Declare @Next int

Set @Next=''

While 1=1

Begin

Select @Next=uv_employee_id

From orc_tb_skills

if @next is null

break

else

select numofoccu from dupidkel-------->another new table created for duplicated id n its numofoccurence

SELECT CASE WHEN numofoccu > 1 THEN

exec (SELECT @empskill2 = uv_skill +'/'+ @empskill2, @empskill3 = uv_level + '/' + @empskill3 ,@empskill4 = uv_year + '/' + @empskill4,@empskill5 = uv_remarks + '/' + @empskill5

FROM orc_tb_skills)

exec (SELECT distinct uv_employee_id , @empskill2 as 'Skills' ,@empskill3 as 'level',@empskill4 as 'year',@empskill5 as 'remarks'

FROM orc_tb_skills) ------->concat code

insert into dupkelulusan ------> (new table created for concant values on id duplication)[/color]

END

continue

End

==============================================================================

Dear Chirag,

Finally got to look up on stored procedure.. but it seems to be there are some errors on syntax.. which took me the whole day to figure it out.. i m running out of ideas ..it is not running at my logic thinks it should be...

Question(s)

1) my current flow is .. at the stored procedure ---> declarations --->while --->case of numofoccurence from another table dupidkel-----> if > 1 run the concat code & insert into a new table for the concatenated results into concatkelulusan .. n that's about it.. Is there anything worng with the flow or i m just stuck with the syntax error? Enlighten me please

It has been almost like hell a new person in sql meeting so much of complicated real world datas.. with no help nor assistance .. please advise..

hope to hear from u soon chirag

regards,

k