Transposing Rows to Columns Help plz!

Question

Post reply

Transposing Rows to Columns Help plz!

Dfalir

SSCrazy

Points: 2486
More actions
August 7, 2007 at 1:26 am

#118046

Guys Hi,
i need your help on this one please...
I have a table that logs entries for each employee that belongs to a department. Each time an employee exits or enters a door a new record for that employee is created. 6 Typical records for todays date are shown below
Department    - Employee - Entry LOG
IT Department,  Person1,    09:30:00 6th In
IT Department,  Person2,  09:35:00 6th In
IT Department,  Person2,  09:50:00 6th Out
ADMIN Department,  Person3,  09:30 6th In,
ADMIN Department,  Person3,  09:40 6th Out,
ADMIN Department,  Person3,  10:30 7th In
(6th in, means, the person entered 6th floor.. etc)
What i want to do is for each person to join all his records in one single row.In other words the result should be like
Department   - Person - Entry Log
IT Department,  Person1,  09:30:00 6th In
IT Department,  Person2,  09:35:00 6th In - 09:50:00 6th Out
ADMIN Department,  Person3,  09:30 6th In - 09:40 6th Out - 10:30 7th In

Could someone help. To make it easier suppose we only want the first 20 entries.

"If you want to get to the top, prepare to kiss alot of bottom"

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

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

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 1

jvElecenter

SSCarpal Tunnel

Points: 4502

August 7, 2007 at 1:50 am

#725035

Hi aren't u using reporting services ?

JV

Dfalir SSCrazy Points: 2486 More actions · Answer 2

YES! CAN I DO THIS THROUGH REPORTING SERVICES????

"If you want to get to the top, prepare to kiss alot of bottom"

bledu SSChampion Points: 12335 More actions · Answer 3

yes you can, goto http://www.gotreportviewer.com to get examples of usage of reports. what you might want to use is called a matrix report. you can do it in crystal reports as well if you are using that

Everything you can imagine is real.

Dfalir SSCrazy Points: 2486 More actions · Answer 4

The problem with matrix is that it does not concatenate values. I do not want a seperate column each time an employee enters or exits the building. Reporting services does not do this. It creates a new column for the same employee, or a new row.

"If you want to get to the top, prepare to kiss alot of bottom"

bledu SSChampion Points: 12335 More actions · Answer 5

what exactly do you want to do then? i am a bit confused.

do you want the data in a row to all show in one column?

Everything you can imagine is real.

bledu SSChampion Points: 12335 More actions · Answer 6

oh je comprends maintenant

you can goto to this post

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=379131

Everything you can imagine is real.

Dfalir SSCrazy Points: 2486 More actions · Answer 7

Thank you for your help the problem is this

I want a SINGLE and ONLY row for each employee that contains ALL its entries in a SINGLE row.

Look at the example above please.

if i have employee Person 1 that in the table has 3 records, that is it has entered/exited the buiylding three times, thus 3 records exist in the table for the employee, i want the report to contain a single row with only 3 Columns:

Department - Employee - entryLOG

dep1 person1 entry1 + entry2 + entry3

dep1 person2 entryA + entryB .... (the number of his entries)

do you understand? any ideas?

"If you want to get to the top, prepare to kiss alot of bottom"

bledu SSChampion Points: 12335 More actions · Answer 8

Dfalir,

yes i understand, if you goto to the post above that i have put, a similar problem is being solved. if you have problems changing the code to suit your needs let us know.

Everything you can imagine is real.

Dfalir SSCrazy Points: 2486 More actions · Answer 9

OK mate i ll try and keep you informed! 🙂

thank you for your time, ill post soon

"If you want to get to the top, prepare to kiss alot of bottom"

Dfalir SSCrazy Points: 2486 More actions · Answer 10

i am not able to do that.. i have wasted one day. 🙁 if i give you the records and the table code can you try for me pls??

"If you want to get to the top, prepare to kiss alot of bottom"

bledu SSChampion Points: 12335 More actions · Answer 11

yea just post the table ddl and some sample code, as long as it not confidential

Everything you can imagine is real.

Dfalir SSCrazy Points: 2486 More actions · Answer 12

OK bledu listen to this

DROP TABLE TBL

CREATE TABLE Tbl (EMPLOYEE NVARchar(50), ENTLOG Nvarchar(50));

INSERT Tbl VALUES('A' , 'Smith');

INSERT Tbl VALUES('A' , 'Jones');

INSERT Tbl VALUES('B' , 'Green');

INSERT Tbl VALUES('B' , 'West');

INSERT Tbl VALUES('B' , 'Johnson');

SELECT * FROM Tbl;

/*listing 1 below*/

SELECT t1.EMPLOYEE,

COALESCE( MIN( CASE WHEN t1.cnt = 1 THEN t1.ENTLOG END) , '' ) + '\n ' +

COALESCE( MIN( CASE WHEN t1.cnt = 2 THEN t1.ENTLOG END) , '' ) + '\n ' +

COALESCE( MIN( CASE WHEN t1.cnt = 3 THEN t1.ENTLOG END) , '' ) AS ENTLOGs

FROM (SELECT t1.EMPLOYEE, t1.ENTLOG,

(SELECT COUNT(*) FROM Tbl t2

WHERE t1.EMPLOYEE= t2.EMPLOYEE And t2.ENTLOG <= t1.ENTLOG) AS Cnt

FROM Tbl t1

GROUP BY t1.EMPLOYEE, t1.ENTLOG) t1

GROUP BY t1.EMPLOYEE;

execute the above and you will see what i am trying to make.

Now modify the listing one so instead getting values from the table tbl, to get values from a view directly derived from the tbl.

CREATE VIEW TBLVIEW AS

SELECT * FROM Tbl

replacing now the tbl in the from clause, with the name of the view, the query still works, right?

However from an exact SAME view in datatypes but with 147.000 rows instead of 5 , this query produces INTERNAL SERVER ERROR?? FOR GODS SAKE!

any ideas??

"If you want to get to the top, prepare to kiss alot of bottom"

bledu SSChampion Points: 12335 More actions · Answer 13

what is the maximum size of the characters you get when you do a select on a single person. you might be bursting the limit

do a select sum(count()) on your table i.e.

select

sum(len(ENTLOG))

from

Tbl

where EMPLOYEE = 'B'

having sum(len(entlog))>8000

Everything you can imagine is real.

Dfalir SSCrazy Points: 2486 More actions · Answer 14

Thank you bledu for your time and help. I believe the internal sql error is caused from performance limitations. I have enough hard drive space, its probably the 1 gig of mem. I search around internet, they said this probably is the cause.

However i seriously doubt the above. Also, the lentgh is definately less than 8000. i do not know how to fix it, but i tried a different course and it worked.

once again thank you for your time,

i really appreciated.

"If you want to get to the top, prepare to kiss alot of bottom"