Getting Master Details info from table

[pb2000]

Hello,

I learn SQL Server from the beginning, being newbie in this field.

I have exemplary table

ID Device UsedBy From To
--------------------------------------------------
1. PC1 Mark 2002/02/02 2003/02/02
2. PC1 Bryan 2003/02/02 2004/02/02
3. PC1 Chris 2004/02/02 2005/02/02


I would like to obtain single column:

ID Value
--------------------------------------------------
1. PC1
2. Mark, 2002/02/02, 2003/02,02
3. Bryan, 2003/02/02, 2004/02,02
4. Chris, 2004/02/02, 2005/02,02

Could you help with that. Possibly it is trivial, for me still hard.
Thank You in advance.
Paul

[yarbrough40]

I don't understand why you would want the first record which has the ID of "1" to show with the result of just "PC1" considering that ID "1" has associated columns.
I think this might be what you want...

SELECT [ID],  [UsedBy] + ', ' + [From] + ', ' + [To] as PC1
WHERE Device = 'PC1'

enjoy!

[pb2000]

Thank You for the answer.

Not exactly.
I obtain with Your answer:

ID Value1 Value2
----------------------------------------------------------
1. PC1, Mark, 2002/02/02
2 PC1, Bryan, 2003/02/02
3. PC1, Chris, 2004/02/02

But I want for multiplied value1 (PC1) Collect all data in 1 column, not row
Namely,

ID Value1
----------------------------------------------------------
1. PC1
2. Mark, 2002/02/02
3. Bryan, 2003/02/02
4. Chris, 2004/02/02

[yarbrough40]

But I want for multiplied value1 (PC1) Collect all data in 1 column, not row

I do not understand this statement at all.

please tell me if I have this right...

you have a table with five columns and some data:
ID ----------Device------- UsedBy---------- From----------- To

1.------------ PC1 ---------Mark ---------2002/02/02-------2003/02/02
2.------------ PC1 --------Bryan ---------2003/02/02 ------2004/02/02
3.------------ PC1 --------Chris ----------2004/02/02 ------2005/02/02


and you want a result set with only two columns. the first column is ID and the second is a concatenation of the other columns with a comma between the values. Except that the very first result needs to be ID=1 and Value = PC1

ID--------------------Value

1. -------------PC1
2. -------------Mark, 2002/02/02, 2003/02/02
3. -------------Bryan, 2003/02/02, 2004/02/02
4. -------------Chris, 2004/02/02, 2005/02/02

is this correct? if so your first result (ID = 1, Value = PC1) does not make much sense to me. Are you sure this is what you want? also see that your ID values are associated with different records from your original table.e.g. ID #2 should be Bryan, not Mark.. very confusing.


(this is what I think you need - please note the column names)
ID--------------------PC1

1. -------------Mark, 2002/02/02, 2003/02/02
2. -------------Bryan, 2003/02/02, 2004/02/02
3. -------------Chris, 2004/02/02, 2005/02/02

[pb2000]

Thank You for the answer. Being newbie I confused simple thema.