June 23, 2011 at 5:02 am
I have a table(DiaryEntries) that hold the following fields:
Id, EmpId, Date, message.
Each EmpId can have more than one entry in the table
e.g.
1, 123, 20/09/2001, Test
1, 123, 21/09/2001, Test2
1, 123, 21/09/2001, Test3
What I want to do is write a select statement that will write these out for each empId in one line
Select empId as EmpNumber, Date as Date1, Message as Message1, Date as Date2, Message as Message2 .....etc
e.g.
123, 20/09/2001, Test, 21/09/2001, Test2, 21/09/2001, Test3
June 23, 2011 at 6:48 am
Is this what you looking for
CREATE TABLE DiaryEntries(
[ID] [VARCHAR](10) NULL,
EmpId [VARCHAR](10) null,
[date] [VARCHAR] (10)null,
[message][VARCHAR](10) null,
)
DROP TABLE DiaryEntries;
INSERT INTO DiaryEntries
VALUES('1','123','2092001','TEST')
INSERT INTO DiaryEntries
VALUES('1','123','21092001','TEST2')
INSERT INTO DiaryEntries
VALUES('1','123','21092001','TEST')
SELECT * FROM DiaryEntries
select empid,empid+','+convert(varchar,date,100) as EmpNumber
from diaryEntries
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
June 23, 2011 at 7:57 am
It's hard to tell based on your description whether you want these values concatenated (All values in one column) or pivoted (each value in its own column, but all on one row).
That said, links to methods for doing both are in my signature. (String Concatenation and Crosstabs/Pivots)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply