SQL dilema

20 Jan 2006 2:26 AM

T

I have a table with the folowing few lines of data.
4460     Alexander, Julie A.    12    5    11/10/2005    0.0
4460     Alexander, Julie A.    12    5    11/11/2005    0.0
4460     Alexander, Julie A.    12    5    11/14/2005    0.0
4460     Alexander, Julie A.    12    5    11/15/2005    0.0
4460     Alexander, Julie A.    12    5    11/16/2005    0.0
4460     Alexander, Julie A.    12    5    11/17/2005    0.0
4460     Alexander, Julie A.    12    5    11/18/2005    0.0
4460     Alexander, Julie A.    12    5    11/21/2005    0.0
3837     Anderson, Paula C.    10    5    11/10/2005    0.60000002
3837     Anderson, Paula C.    10    5    11/11/2005    0.0
3837     Anderson, Paula C.    10    5    11/14/2005    0.0
3837     Anderson, Paula C.    10    5    11/15/2005    0.0
3837     Anderson, Paula C.    10    5    11/17/2005    0.0
3837     Anderson, Paula C.    10    5    11/18/2005    2.3
3837     Anderson, Paula C.    10    5    11/21/2005
4227     Andreoli, Elizabeth A.    10    2    11/10/2005    0.0
4227     Andreoli, Elizabeth A.    10    2    11/11/2005    0.0
4227     Andreoli, Elizabeth A.    10    2    11/14/2005    0.0
4227     Andreoli, Elizabeth A.    10    2    11/15/2005    0.0
4227     Andreoli, Elizabeth A.    10    2    11/16/2005    0.0
4227     Andreoli, Elizabeth A.    10    2    11/17/2005    0.0
4227     Andreoli, Elizabeth A.    10    2    11/18/2005    0.0
4227     Andreoli, Elizabeth A.    10    2    11/21/2005    0.0
4040     Arens, Carolyn Sue    08    5    11/16/2005    3.8
4040     Arens, Carolyn Sue    08    5    11/21/2005    2.7
3611     Armstrong, Joe E.    01    1    11/15/2005
4408     Aronson, Daphne H.    01    3    11/10/2005    0.0
4408     Aronson, Daphne H.    01    3    11/11/2005    0.0
4408     Aronson, Daphne H.    01    3    11/14/2005    0.0
3795     Barrett, Jennifer K.    02    3    11/11/2005    0.0
3795     Barrett, Jennifer K.    02    3    11/18/2005    0.0
3665     Bridges, Tracy T.    01    1    11/11/2005    0.0
3665     Bridges, Tracy T.    01    1    11/16/2005    2.0
3665     Bridges, Tracy T.    01    1    11/18/2005    0.0
4410     Bullock, Michael C.    02    2    11/10/2005    0.0
4410     Bullock, Michael C.    02    2    11/11/2005    0.0
4410     Bullock, Michael C.    02    2    11/14/2005    0.0
4410     Bullock, Michael C.    02    2    11/15/2005    0.0
4410     Bullock, Michael C.    02    2    11/16/2005    0.0
4410     Bullock, Michael C.    02    2    11/17/2005    0.0
4410     Bullock, Michael C.    02    2    11/18/2005    0.0
4410     Bullock, Michael C.    02    2    11/21/2005    0.0
4071     Bunger, C. Kenneth    01    3    11/10/2005    0.5
4071     Bunger, C. Kenneth    01    3    11/11/2005    2.3
4071     Bunger, C. Kenneth    01    3    11/14/2005    1.1
4071     Bunger, C. Kenneth    01    3    11/15/2005    0.0
4071     Bunger, C. Kenneth    01    3    11/16/2005    0.0
4071     Bunger, C. Kenneth    01    3    11/17/2005    2.0
4071     Bunger, C. Kenneth    01    3    11/18/2005    0.0
4071     Bunger, C. Kenneth    01    3    11/21/2005    1.8
4428     Coats, Jeffrey A.    05    2    11/10/2005    0.0

I simply wish to shrink to one line per name so that the dates(all per
person) are in a singe column.
example.
4071     Bunger, C. Kenneth    11/15/2005,11/11/2005,11/11/2005
Any idea on the select given the way the data is stored ?

20 Jan 2006 3:01 AM

Jonathan Chong

I would suggest to store the dates data in separate table and link your
person's detail to this dates table.

Like:
PersonsTable:
4460 Alexander, Julie A. 12 5

DatesTable
4460 11/10/2005
4460 11/11/2005
4460 11/14/2005
4460 11/15/2005
4460 11/16/2005
4460 11/17/2005
4460 11/18/2005
4460 11/21/2005

Show quote

"T" <T@discussions.microsoft.com> wrote in message
news:5AD26F74-591D-4E50-93AE-9121AC0C27CC@microsoft.com...
> I have a table with the folowing few lines of data.
> 4460 Alexander, Julie A. 12 5 11/10/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/11/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/14/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/15/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/16/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/17/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/18/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/21/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/10/2005 0.60000002
> 3837 Anderson, Paula C. 10 5 11/11/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/14/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/15/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/17/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/18/2005 2.3
> 3837 Anderson, Paula C. 10 5 11/21/2005
> 4227 Andreoli, Elizabeth A. 10 2 11/10/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/11/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/14/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/15/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/16/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/17/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/18/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/21/2005 0.0
> 4040 Arens, Carolyn Sue 08 5 11/16/2005 3.8
> 4040 Arens, Carolyn Sue 08 5 11/21/2005 2.7
> 3611 Armstrong, Joe E. 01 1 11/15/2005
> 4408 Aronson, Daphne H. 01 3 11/10/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/11/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/14/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/11/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/18/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/11/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/16/2005 2.0
> 3665 Bridges, Tracy T. 01 1 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/10/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/11/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/14/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/15/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/16/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/17/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/21/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/10/2005 0.5
> 4071 Bunger, C. Kenneth 01 3 11/11/2005 2.3
> 4071 Bunger, C. Kenneth 01 3 11/14/2005 1.1
> 4071 Bunger, C. Kenneth 01 3 11/15/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/16/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/17/2005 2.0
> 4071 Bunger, C. Kenneth 01 3 11/18/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/21/2005 1.8
> 4428 Coats, Jeffrey A. 05 2 11/10/2005 0.0
>
> I simply wish to shrink to one line per name so that the dates(all per
> person) are in a singe column.
> example.
> 4071 Bunger, C. Kenneth 11/15/2005,11/11/2005,11/11/2005
> Any idea on the select given the way the data is stored ?

20 Jan 2006 4:26 AM

Uri Dimant

T
CREATE TABLE #Demo (
    idNo int identity(1,1),
    colA int,
    colB int
)

INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)

PRINT 'Table'
SELECT * FROM #Demo

PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
     FROM #Demo A JOIN #Demo B
       ON A.idNo <> B.idNo
      AND A.colA = B.colA
      AND A.colB = B.colB)

PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
     FROM #Demo A JOIN #Demo B
       ON A.idNo < B.idNo -- < this time, not <>
      AND A.colA = B.colA
      AND A.colB = B.colB)

DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
     FROM #Demo A JOIN #Demo B
       ON A.idNo < B.idNo -- < this time, not <>
      AND A.colA = B.colA
      AND A.colB = B.colB)

PRINT 'Cleaned-up Table'
SELECT * FROM #Demo

DROP TABLE #Demo

Show quote

"T" <T@discussions.microsoft.com> wrote in message
news:5AD26F74-591D-4E50-93AE-9121AC0C27CC@microsoft.com...
>I have a table with the folowing few lines of data.
> 4460 Alexander, Julie A. 12 5 11/10/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/11/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/14/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/15/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/16/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/17/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/18/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/21/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/10/2005 0.60000002
> 3837 Anderson, Paula C. 10 5 11/11/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/14/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/15/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/17/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/18/2005 2.3
> 3837 Anderson, Paula C. 10 5 11/21/2005
> 4227 Andreoli, Elizabeth A. 10 2 11/10/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/11/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/14/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/15/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/16/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/17/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/18/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/21/2005 0.0
> 4040 Arens, Carolyn Sue 08 5 11/16/2005 3.8
> 4040 Arens, Carolyn Sue 08 5 11/21/2005 2.7
> 3611 Armstrong, Joe E. 01 1 11/15/2005
> 4408 Aronson, Daphne H. 01 3 11/10/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/11/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/14/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/11/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/18/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/11/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/16/2005 2.0
> 3665 Bridges, Tracy T. 01 1 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/10/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/11/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/14/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/15/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/16/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/17/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/21/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/10/2005 0.5
> 4071 Bunger, C. Kenneth 01 3 11/11/2005 2.3
> 4071 Bunger, C. Kenneth 01 3 11/14/2005 1.1
> 4071 Bunger, C. Kenneth 01 3 11/15/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/16/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/17/2005 2.0
> 4071 Bunger, C. Kenneth 01 3 11/18/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/21/2005 1.8
> 4428 Coats, Jeffrey A. 05 2 11/10/2005 0.0
>
> I simply wish to shrink to one line per name so that the dates(all per
> person) are in a singe column.
> example.
> 4071 Bunger, C. Kenneth 11/15/2005,11/11/2005,11/11/2005
> Any idea on the select given the way the data is stored ?

20 Jan 2006 4:41 AM

Jonathan Chong

Hi Uri,
I think T is not asking on how to get rid of duplicate rows but rather on
how to display multiple dates for a same person on a row.

Show quote

"Uri Dimant" <u***@iscar.co.il> wrote in message
news:OHWk2mXHGHA.3056@TK2MSFTNGP09.phx.gbl...
> T
> CREATE TABLE #Demo (
>     idNo int identity(1,1),
>     colA int,
>     colB int
> )
>
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
>
> PRINT 'Table'
> SELECT * FROM #Demo
>
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
>   (SELECT B.idNo
>      FROM #Demo A JOIN #Demo B
>        ON A.idNo <> B.idNo
>       AND A.colA = B.colA
>       AND A.colB = B.colB)
>
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
>   (SELECT B.idNo
>      FROM #Demo A JOIN #Demo B
>        ON A.idNo < B.idNo -- < this time, not <>
>       AND A.colA = B.colA
>       AND A.colB = B.colB)
>
> DELETE FROM #Demo
> WHERE idNo IN
>   (SELECT B.idNo
>      FROM #Demo A JOIN #Demo B
>        ON A.idNo < B.idNo -- < this time, not <>
>       AND A.colA = B.colA
>       AND A.colB = B.colB)
>
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
>
> DROP TABLE #Demo
>
>
>
> "T" <T@discussions.microsoft.com> wrote in message
> news:5AD26F74-591D-4E50-93AE-9121AC0C27CC@microsoft.com...
> >I have a table with the folowing few lines of data.
> > 4460 Alexander, Julie A. 12 5 11/10/2005 0.0
> > 4460 Alexander, Julie A. 12 5 11/11/2005 0.0
> > 4460 Alexander, Julie A. 12 5 11/14/2005 0.0
> > 4460 Alexander, Julie A. 12 5 11/15/2005 0.0
> > 4460 Alexander, Julie A. 12 5 11/16/2005 0.0
> > 4460 Alexander, Julie A. 12 5 11/17/2005 0.0
> > 4460 Alexander, Julie A. 12 5 11/18/2005 0.0
> > 4460 Alexander, Julie A. 12 5 11/21/2005 0.0
> > 3837 Anderson, Paula C. 10 5 11/10/2005 0.60000002
> > 3837 Anderson, Paula C. 10 5 11/11/2005 0.0
> > 3837 Anderson, Paula C. 10 5 11/14/2005 0.0
> > 3837 Anderson, Paula C. 10 5 11/15/2005 0.0
> > 3837 Anderson, Paula C. 10 5 11/17/2005 0.0
> > 3837 Anderson, Paula C. 10 5 11/18/2005 2.3
> > 3837 Anderson, Paula C. 10 5 11/21/2005
> > 4227 Andreoli, Elizabeth A. 10 2 11/10/2005 0.0
> > 4227 Andreoli, Elizabeth A. 10 2 11/11/2005 0.0
> > 4227 Andreoli, Elizabeth A. 10 2 11/14/2005 0.0
> > 4227 Andreoli, Elizabeth A. 10 2 11/15/2005 0.0
> > 4227 Andreoli, Elizabeth A. 10 2 11/16/2005 0.0
> > 4227 Andreoli, Elizabeth A. 10 2 11/17/2005 0.0
> > 4227 Andreoli, Elizabeth A. 10 2 11/18/2005 0.0
> > 4227 Andreoli, Elizabeth A. 10 2 11/21/2005 0.0
> > 4040 Arens, Carolyn Sue 08 5 11/16/2005 3.8
> > 4040 Arens, Carolyn Sue 08 5 11/21/2005 2.7
> > 3611 Armstrong, Joe E. 01 1 11/15/2005
> > 4408 Aronson, Daphne H. 01 3 11/10/2005 0.0
> > 4408 Aronson, Daphne H. 01 3 11/11/2005 0.0
> > 4408 Aronson, Daphne H. 01 3 11/14/2005 0.0
> > 3795 Barrett, Jennifer K. 02 3 11/11/2005 0.0
> > 3795 Barrett, Jennifer K. 02 3 11/18/2005 0.0
> > 3665 Bridges, Tracy T. 01 1 11/11/2005 0.0
> > 3665 Bridges, Tracy T. 01 1 11/16/2005 2.0
> > 3665 Bridges, Tracy T. 01 1 11/18/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/10/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/11/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/14/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/15/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/16/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/17/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/18/2005 0.0
> > 4410 Bullock, Michael C. 02 2 11/21/2005 0.0
> > 4071 Bunger, C. Kenneth 01 3 11/10/2005 0.5
> > 4071 Bunger, C. Kenneth 01 3 11/11/2005 2.3
> > 4071 Bunger, C. Kenneth 01 3 11/14/2005 1.1
> > 4071 Bunger, C. Kenneth 01 3 11/15/2005 0.0
> > 4071 Bunger, C. Kenneth 01 3 11/16/2005 0.0
> > 4071 Bunger, C. Kenneth 01 3 11/17/2005 2.0
> > 4071 Bunger, C. Kenneth 01 3 11/18/2005 0.0
> > 4071 Bunger, C. Kenneth 01 3 11/21/2005 1.8
> > 4428 Coats, Jeffrey A. 05 2 11/10/2005 0.0
> >
> > I simply wish to shrink to one line per name so that the dates(all per
> > person) are in a singe column.
> > example.
> > 4071 Bunger, C. Kenneth 11/15/2005,11/11/2005,11/11/2005
> > Any idea on the select given the way the data is stored ?
>
>

20 Jan 2006 5:22 AM

Uri Dimant

Yes ,I know

There is a SELECT statement. I tried to give him an idea and a direction to
how to solve the problem, that's all.

PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
     FROM #Demo A JOIN #Demo B
       ON A.idNo < B.idNo -- < this time, not <>
      AND A.colA = B.colA
      AND A.colB = B.colB)

Show quote

"Jonathan Chong" <noj0nathan@3exp.comaps> wrote in message
news:O34G0vXHGHA.648@TK2MSFTNGP14.phx.gbl...
> Hi Uri,
> I think T is not asking on how to get rid of duplicate rows but rather on
> how to display multiple dates for a same person on a row.
>
> "Uri Dimant" <u***@iscar.co.il> wrote in message
> news:OHWk2mXHGHA.3056@TK2MSFTNGP09.phx.gbl...
>> T
>> CREATE TABLE #Demo (
>>     idNo int identity(1,1),
>>     colA int,
>>     colB int
>> )
>>
>> INSERT INTO #Demo(colA,colB) VALUES (1,6)
>> INSERT INTO #Demo(colA,colB) VALUES (1,6)
>> INSERT INTO #Demo(colA,colB) VALUES (2,4)
>> INSERT INTO #Demo(colA,colB) VALUES (3,3)
>> INSERT INTO #Demo(colA,colB) VALUES (4,2)
>> INSERT INTO #Demo(colA,colB) VALUES (3,3)
>> INSERT INTO #Demo(colA,colB) VALUES (5,1)
>> INSERT INTO #Demo(colA,colB) VALUES (8,1)
>>
>> PRINT 'Table'
>> SELECT * FROM #Demo
>>
>> PRINT 'Duplicates in Table'
>> SELECT * FROM #Demo
>> WHERE idNo IN
>>   (SELECT B.idNo
>>      FROM #Demo A JOIN #Demo B
>>        ON A.idNo <> B.idNo
>>       AND A.colA = B.colA
>>       AND A.colB = B.colB)
>>
>> PRINT 'Duplicates to Delete'
>> SELECT * FROM #Demo
>> WHERE idNo IN
>>   (SELECT B.idNo
>>      FROM #Demo A JOIN #Demo B
>>        ON A.idNo < B.idNo -- < this time, not <>
>>       AND A.colA = B.colA
>>       AND A.colB = B.colB)
>>
>> DELETE FROM #Demo
>> WHERE idNo IN
>>   (SELECT B.idNo
>>      FROM #Demo A JOIN #Demo B
>>        ON A.idNo < B.idNo -- < this time, not <>
>>       AND A.colA = B.colA
>>       AND A.colB = B.colB)
>>
>> PRINT 'Cleaned-up Table'
>> SELECT * FROM #Demo
>>
>> DROP TABLE #Demo
>>
>>
>>
>> "T" <T@discussions.microsoft.com> wrote in message
>> news:5AD26F74-591D-4E50-93AE-9121AC0C27CC@microsoft.com...
>> >I have a table with the folowing few lines of data.
>> > 4460 Alexander, Julie A. 12 5 11/10/2005 0.0
>> > 4460 Alexander, Julie A. 12 5 11/11/2005 0.0
>> > 4460 Alexander, Julie A. 12 5 11/14/2005 0.0
>> > 4460 Alexander, Julie A. 12 5 11/15/2005 0.0
>> > 4460 Alexander, Julie A. 12 5 11/16/2005 0.0
>> > 4460 Alexander, Julie A. 12 5 11/17/2005 0.0
>> > 4460 Alexander, Julie A. 12 5 11/18/2005 0.0
>> > 4460 Alexander, Julie A. 12 5 11/21/2005 0.0
>> > 3837 Anderson, Paula C. 10 5 11/10/2005 0.60000002
>> > 3837 Anderson, Paula C. 10 5 11/11/2005 0.0
>> > 3837 Anderson, Paula C. 10 5 11/14/2005 0.0
>> > 3837 Anderson, Paula C. 10 5 11/15/2005 0.0
>> > 3837 Anderson, Paula C. 10 5 11/17/2005 0.0
>> > 3837 Anderson, Paula C. 10 5 11/18/2005 2.3
>> > 3837 Anderson, Paula C. 10 5 11/21/2005
>> > 4227 Andreoli, Elizabeth A. 10 2 11/10/2005 0.0
>> > 4227 Andreoli, Elizabeth A. 10 2 11/11/2005 0.0
>> > 4227 Andreoli, Elizabeth A. 10 2 11/14/2005 0.0
>> > 4227 Andreoli, Elizabeth A. 10 2 11/15/2005 0.0
>> > 4227 Andreoli, Elizabeth A. 10 2 11/16/2005 0.0
>> > 4227 Andreoli, Elizabeth A. 10 2 11/17/2005 0.0
>> > 4227 Andreoli, Elizabeth A. 10 2 11/18/2005 0.0
>> > 4227 Andreoli, Elizabeth A. 10 2 11/21/2005 0.0
>> > 4040 Arens, Carolyn Sue 08 5 11/16/2005 3.8
>> > 4040 Arens, Carolyn Sue 08 5 11/21/2005 2.7
>> > 3611 Armstrong, Joe E. 01 1 11/15/2005
>> > 4408 Aronson, Daphne H. 01 3 11/10/2005 0.0
>> > 4408 Aronson, Daphne H. 01 3 11/11/2005 0.0
>> > 4408 Aronson, Daphne H. 01 3 11/14/2005 0.0
>> > 3795 Barrett, Jennifer K. 02 3 11/11/2005 0.0
>> > 3795 Barrett, Jennifer K. 02 3 11/18/2005 0.0
>> > 3665 Bridges, Tracy T. 01 1 11/11/2005 0.0
>> > 3665 Bridges, Tracy T. 01 1 11/16/2005 2.0
>> > 3665 Bridges, Tracy T. 01 1 11/18/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/10/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/11/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/14/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/15/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/16/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/17/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/18/2005 0.0
>> > 4410 Bullock, Michael C. 02 2 11/21/2005 0.0
>> > 4071 Bunger, C. Kenneth 01 3 11/10/2005 0.5
>> > 4071 Bunger, C. Kenneth 01 3 11/11/2005 2.3
>> > 4071 Bunger, C. Kenneth 01 3 11/14/2005 1.1
>> > 4071 Bunger, C. Kenneth 01 3 11/15/2005 0.0
>> > 4071 Bunger, C. Kenneth 01 3 11/16/2005 0.0
>> > 4071 Bunger, C. Kenneth 01 3 11/17/2005 2.0
>> > 4071 Bunger, C. Kenneth 01 3 11/18/2005 0.0
>> > 4071 Bunger, C. Kenneth 01 3 11/21/2005 1.8
>> > 4428 Coats, Jeffrey A. 05 2 11/10/2005 0.0
>> >
>> > I simply wish to shrink to one line per name so that the dates(all per
>> > person) are in a singe column.
>> > example.
>> > 4071 Bunger, C. Kenneth 11/15/2005,11/11/2005,11/11/2005
>> > Any idea on the select given the way the data is stored ?
>>
>>
>
>

20 Jan 2006 5:51 AM

--CELKO--

>> I simply wish to shrink to one line per name so that the dates (all per person) are in a singe column. <<

This is called "I am smarter than Dr. Codd and wish to violate First
Normal Form (1NF), the very foundation the RM theory"; could us why
you are smarter than 30+ years of RDBMS theory?

Please read at least one RDBMS book that will give you the basic
definitions. You missed the part about how a column has to be atomic
and scalar -- it should be in chapter 1, 2 or 3 depending how much
history they do in the book.

Oh, this also violates the **most fundamental principle" of a tiered
architecture,which is that display is done in the front end and NEVER
-- repeat NEVER-- in the back end.

20 Jan 2006 8:00 AM

Tony Rogerson

> This is called "I am smarter than Dr. Codd and wish to violate First
> Normal Form (1NF), the very foundation the RM theory"; could us why
> you are smarter than 30+ years of RDBMS theory?

30+ years ago they didn't have client/server and the requirements of the
user where simple in their extreme.

The environment and challenges of todays technical and business environment
are such that things like the OP wants are common place.

If you did any coding in the past 7 years you would know and understand
that, I think writing books and papers has taken you away from what the real
world is doing and needs.

Stop trying to pull us back into your time.

> Oh, this also violates the **most fundamental principle" of a tiered
> architecture,which is that display is done in the front end and NEVER
> -- repeat NEVER-- in the back end.

Unfortunetly we don't all own super computers so the price of passing back
1,000,000 rows to the client just to get the 10 rows on page number 2 is too
heavy a price to pay; so, we need to do all this chopping (paging) in the
backend inside of SQL Server.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1137736287.137440.148450@f14g2000cwb.googlegroups.com...
>>> I simply wish to shrink to one line per name so that the dates (all
>>> per person) are in a singe column. <<
>
> This is called "I am smarter than Dr. Codd and wish to violate First
> Normal Form (1NF), the very foundation the RM theory"; could us why
> you are smarter than 30+ years of RDBMS theory?
>
> Please read at least one RDBMS book that will give you the basic
> definitions. You missed the part about how a column has to be atomic
> and scalar -- it should be in chapter 1, 2 or 3 depending how much
> history they do in the book.
>
> Oh, this also violates the **most fundamental principle" of a tiered
> architecture,which is that display is done in the front end and NEVER
> -- repeat NEVER-- in the back end.
>

20 Jan 2006 2:52 PM

Alexander Kuznetsov

> Oh, this also violates the **most fundamental principle" of a tiered
> architecture,which is that display is done in the front end and NEVER
> -- repeat NEVER-- in the back end.

What is the logic for that "most fundamental principle"? Getting around
it frequently gives 5+ times better performance, and more reusable
code.

Why is string concatenation so fundamentally different from numeric
aggregation, which is perfectly OK, like this
select sum(amount) from sales

20 Jan 2006 4:32 PM

Anith Sen

>> Why is string concatenation so fundamentally different from numeric
>> aggregation, which is perfectly OK, like this select sum(amount) from
>> sales

String concatenation requires essential ordering of the values, while
numeric aggregation does not.

--
Anith

20 Jan 2006 5:11 PM

Alexander Kuznetsov

>String concatenation requires essential ordering of the values, while
>numeric aggregation does not.

not necessarily. For instance, here is the OP's request for the very
top posting:

11/15/2005,11/11/2005,11/11/2005

Still, maybe sending 11/15/2005,11/11/2005,11/11/2005 over the network
is fundamentally wrong from the theory of RDBMS perspective. I would
not argue with that. My point is different: sending concatenated string
11/15/2005,11/11/2005,11/11/2005 over the network may be several times
more efficient in terms of network usage. If middle tier is supposed to
do the concatenation, then that layer must run on the same server.

>From the practical point of view, it is way more difficult to implement

and way less efficient to run as opposed to concatenating inside the
database.
So, from the perspective of the whole system, concatenation belongs on
the database server.

What do you think?

20 Jan 2006 5:35 PM

Jim Underwood

I dont think any of the 1st, 2nd, or 3rd normal form rules apply to
reporting. They certainly apply to how you store the data, but the entire
point of queries and views is to DENORMALZE your data into a format usable
by the application and the user. There is absolutely nothgin wrong (from a
normalization standpoint) with any way that you choose to organize your
output.

However, there are lots of code maintenance and scalability reasons why you
may not want to do these things.

In the end sometimes our requirements force us to use techniques we would
rather not.

Minimizing the data sent across the network is a perfectly valid reason
using this type of code, provided you have considered the impact on the
database server's memory and CPU. Any approach will improve one resource at
the cost of another.

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1137777110.872348.42710@g49g2000cwa.googlegroups.com...
> >String concatenation requires essential ordering of the values, while
> >numeric aggregation does not.
>
> not necessarily. For instance, here is the OP's request for the very
> top posting:
>
> 11/15/2005,11/11/2005,11/11/2005
>
> Still, maybe sending 11/15/2005,11/11/2005,11/11/2005 over the network
> is fundamentally wrong from the theory of RDBMS perspective. I would
> not argue with that. My point is different: sending concatenated string
> 11/15/2005,11/11/2005,11/11/2005 over the network may be several times
> more efficient in terms of network usage. If middle tier is supposed to
> do the concatenation, then that layer must run on the same server.
> >From the practical point of view, it is way more difficult to implement
> and way less efficient to run as opposed to concatenating inside the
> database.
> So, from the perspective of the whole system, concatenation belongs on
> the database server.
>
> What do you think?
>

20 Jan 2006 6:36 PM

Alexander Kuznetsov

> there are lots of code maintenance and scalability reasons why you
> may not want to do these things.

Jim,
please elaborate on code maintenance reasons. I usually see the
opposite: some presentation layer logic is implemented in VB, then
re-done in a COM middle tier module, then again in a Java module, then
in Crystal reports, then in ASP.Net, whatever else I'm missing. Client
side technologies evolve real fast. Should the presentation layer logic
be done in T-SQL the first time, there would be no need to rewrite it
for every client side technology

20 Jan 2006 6:53 PM

Jim Underwood

Basically, looping through a recordset and doing string concatenation is
usually easier to code in a client app then in SQL. You can run a simple
query, return 2000 rows with little or no effort, then have a line in your
code that checks for a new key and keeps concatenating values to the end of
your output row until it gets to the next key in the dataset.

Multiple joins, unions, temp tables, and cursors can all be used to
accomplish this on the database side, but tend to be a fair bit more complex
than a single if statement inside your application code. It has been my
experience that most developers tend to understand procedural client code
much better than they do any of the SQL conecepts. When I hand off a stored
procedure or a view with multiple unions/outer joins I usually get that deer
in the headlights look from the other party.

Also, I have found that with queries of this type, the data structure, or
the number/type of columns that must be concatenated tends to change far
more frequently than the technology used for presentation on the client.
Granted, this may be specific to the users and apps I have worked on, and is
not so much an issue in this particular case.

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1137782192.091498.297490@g44g2000cwa.googlegroups.com...
> > there are lots of code maintenance and scalability reasons why you
> > may not want to do these things.
>
> Jim,
> please elaborate on code maintenance reasons. I usually see the
> opposite: some presentation layer logic is implemented in VB, then
> re-done in a COM middle tier module, then again in a Java module, then
> in Crystal reports, then in ASP.Net, whatever else I'm missing. Client
> side technologies evolve real fast. Should the presentation layer logic
> be done in T-SQL the first time, there would be no need to rewrite it
> for every client side technology
>

20 Jan 2006 7:21 PM

Tony Rogerson

Hi Jim,

It's no longer the case in SQL Server 2005, you can do very scalable and
fast concatenation using this feature of the FOR XML...

select distinct type,
    (   select name + ', ' as [text()]
        from sys.objects s2
        where s2.type = s1.type
        order by s2.name
        for xml path( '' )
        ) as concatentated_name
from sys.objects s1

I think, for the most part, the days of concatenating in the client app are
over; well, for those things the above can resolve that is which is alot of
things I've come across already!

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23u%23gBLfHGHA.3000@TK2MSFTNGP14.phx.gbl...
> Basically, looping through a recordset and doing string concatenation is
> usually easier to code in a client app then in SQL. You can run a simple
> query, return 2000 rows with little or no effort, then have a line in your
> code that checks for a new key and keeps concatenating values to the end
> of
> your output row until it gets to the next key in the dataset.
>
> Multiple joins, unions, temp tables, and cursors can all be used to
> accomplish this on the database side, but tend to be a fair bit more
> complex
> than a single if statement inside your application code. It has been my
> experience that most developers tend to understand procedural client code
> much better than they do any of the SQL conecepts. When I hand off a
> stored
> procedure or a view with multiple unions/outer joins I usually get that
> deer
> in the headlights look from the other party.
>
> Also, I have found that with queries of this type, the data structure, or
> the number/type of columns that must be concatenated tends to change far
> more frequently than the technology used for presentation on the client.
> Granted, this may be specific to the users and apps I have worked on, and
> is
> not so much an issue in this particular case.
>
>
> "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
> news:1137782192.091498.297490@g44g2000cwa.googlegroups.com...
>> > there are lots of code maintenance and scalability reasons why you
>> > may not want to do these things.
>>
>> Jim,
>> please elaborate on code maintenance reasons. I usually see the
>> opposite: some presentation layer logic is implemented in VB, then
>> re-done in a COM middle tier module, then again in a Java module, then
>> in Crystal reports, then in ASP.Net, whatever else I'm missing. Client
>> side technologies evolve real fast. Should the presentation layer logic
>> be done in T-SQL the first time, there would be no need to rewrite it
>> for every client side technology
>>
>
>

20 Jan 2006 7:49 PM

Jim Underwood

This is something I have not seen yet, since I have limited experience with
either XML or SQL Server 2005.

This does indeed invalidate my concerns, both by simplifying the code
needed, as well as allowing the list of values to be controlled with a
single where clause.

At the risk of getting off topic even more than we already have, does anyone
know of a similar function in Oracle?

Is there any ANSI standard for working with XML in this fashion?

Show quote

"Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message
news:ueKq1afHGHA.3100@tk2msftngp13.phx.gbl...
> Hi Jim,
>
> It's no longer the case in SQL Server 2005, you can do very scalable and
> fast concatenation using this feature of the FOR XML...
>
> select distinct type,
>     (   select name + ', ' as [text()]
>         from sys.objects s2
>         where s2.type = s1.type
>         order by s2.name
>         for xml path( '' )
>         ) as concatentated_name
> from sys.objects s1
>
> I think, for the most part, the days of concatenating in the client app
are
> over; well, for those things the above can resolve that is which is alot
of
> things I've come across already!
>
> Tony.
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23u%23gBLfHGHA.3000@TK2MSFTNGP14.phx.gbl...
> > Basically, looping through a recordset and doing string concatenation is
> > usually easier to code in a client app then in SQL. You can run a
simple
> > query, return 2000 rows with little or no effort, then have a line in
your
> > code that checks for a new key and keeps concatenating values to the end
> > of
> > your output row until it gets to the next key in the dataset.
> >
> > Multiple joins, unions, temp tables, and cursors can all be used to
> > accomplish this on the database side, but tend to be a fair bit more
> > complex
> > than a single if statement inside your application code. It has been my
> > experience that most developers tend to understand procedural client
code
> > much better than they do any of the SQL conecepts. When I hand off a
> > stored
> > procedure or a view with multiple unions/outer joins I usually get that
> > deer
> > in the headlights look from the other party.
> >
> > Also, I have found that with queries of this type, the data structure,
or
> > the number/type of columns that must be concatenated tends to change far
> > more frequently than the technology used for presentation on the client.
> > Granted, this may be specific to the users and apps I have worked on,
and
> > is
> > not so much an issue in this particular case.
> >
> >
> > "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
> > news:1137782192.091498.297490@g44g2000cwa.googlegroups.com...
> >> > there are lots of code maintenance and scalability reasons why you
> >> > may not want to do these things.
> >>
> >> Jim,
> >> please elaborate on code maintenance reasons. I usually see the
> >> opposite: some presentation layer logic is implemented in VB, then
> >> re-done in a COM middle tier module, then again in a Java module, then
> >> in Crystal reports, then in ASP.Net, whatever else I'm missing. Client
> >> side technologies evolve real fast. Should the presentation layer logic
> >> be done in T-SQL the first time, there would be no need to rewrite it
> >> for every client side technology
> >>
> >
> >
>
>

20 Jan 2006 7:56 PM

Tony Rogerson

It's years since I developed using Oracle, and even then it was only for
around 6 months - lots have changed, i think it was 7i or something like
that.

I very very much doubt that the FOR XML is an industrial standard, just a
feature added by MS to SQL Server at internal/partner/community/customer
request.

It's good though - Itzik-Ben Gan first showed me this, the number of times
I've had to code away around what this does in a very simple and clean
statement!

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%239H7TqfHGHA.376@TK2MSFTNGP12.phx.gbl...
> This is something I have not seen yet, since I have limited experience
> with
> either XML or SQL Server 2005.
>
> This does indeed invalidate my concerns, both by simplifying the code
> needed, as well as allowing the list of values to be controlled with a
> single where clause.
>
> At the risk of getting off topic even more than we already have, does
> anyone
> know of a similar function in Oracle?
>
> Is there any ANSI standard for working with XML in this fashion?
>
>
> "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message
> news:ueKq1afHGHA.3100@tk2msftngp13.phx.gbl...
>> Hi Jim,
>>
>> It's no longer the case in SQL Server 2005, you can do very scalable and
>> fast concatenation using this feature of the FOR XML...
>>
>> select distinct type,
>>     (   select name + ', ' as [text()]
>>         from sys.objects s2
>>         where s2.type = s1.type
>>         order by s2.name
>>         for xml path( '' )
>>         ) as concatentated_name
>> from sys.objects s1
>>
>> I think, for the most part, the days of concatenating in the client app
> are
>> over; well, for those things the above can resolve that is which is alot
> of
>> things I've come across already!
>>
>> Tony.
>>
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlserverfaq.com - free video tutorials
>>
>>
>> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
>> news:%23u%23gBLfHGHA.3000@TK2MSFTNGP14.phx.gbl...
>> > Basically, looping through a recordset and doing string concatenation
>> > is
>> > usually easier to code in a client app then in SQL. You can run a
> simple
>> > query, return 2000 rows with little or no effort, then have a line in
> your
>> > code that checks for a new key and keeps concatenating values to the
>> > end
>> > of
>> > your output row until it gets to the next key in the dataset.
>> >
>> > Multiple joins, unions, temp tables, and cursors can all be used to
>> > accomplish this on the database side, but tend to be a fair bit more
>> > complex
>> > than a single if statement inside your application code. It has been
>> > my
>> > experience that most developers tend to understand procedural client
> code
>> > much better than they do any of the SQL conecepts. When I hand off a
>> > stored
>> > procedure or a view with multiple unions/outer joins I usually get that
>> > deer
>> > in the headlights look from the other party.
>> >
>> > Also, I have found that with queries of this type, the data structure,
> or
>> > the number/type of columns that must be concatenated tends to change
>> > far
>> > more frequently than the technology used for presentation on the
>> > client.
>> > Granted, this may be specific to the users and apps I have worked on,
> and
>> > is
>> > not so much an issue in this particular case.
>> >
>> >
>> > "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
>> > news:1137782192.091498.297490@g44g2000cwa.googlegroups.com...
>> >> > there are lots of code maintenance and scalability reasons why you
>> >> > may not want to do these things.
>> >>
>> >> Jim,
>> >> please elaborate on code maintenance reasons. I usually see the
>> >> opposite: some presentation layer logic is implemented in VB, then
>> >> re-done in a COM middle tier module, then again in a Java module, then
>> >> in Crystal reports, then in ASP.Net, whatever else I'm missing. Client
>> >> side technologies evolve real fast. Should the presentation layer
>> >> logic
>> >> be done in T-SQL the first time, there would be no need to rewrite it
>> >> for every client side technology
>> >>
>> >
>> >
>>
>>
>
>

20 Jan 2006 8:01 PM

Alexander Kuznetsov

> doing string concatenation is usually easier to code in a client app then in SQL

not always. I've worked with entry level Crystal Report writers, they
are not supposed to do that. I would give them a stub to work with

create view report11
as
select
'John' first_name, 'Doe' Last_name, '(312)123-4567 work, (630)987-1234
home' Contact_info

> does anyone know of a similar function in Oracle?

an answer to any Oracle question can be found here:

http://asktom.oracle.com/pls/ask/f?p=4950:8:15947178513142262821::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:229614022562

(go to asktom.oracle.com and search on comma separated list)

20 Jan 2006 3:05 PM

Jim Underwood

I think he is talking about a query or view here, not changing the way to
data itself is stored, but rather a simpler way to retrieve it.

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1137736287.137440.148450@f14g2000cwb.googlegroups.com...
> >> I simply wish to shrink to one line per name so that the dates (all

per person) are in a singe column. <<
Show quote

>
> This is called "I am smarter than Dr. Codd and wish to violate First
> Normal Form (1NF), the very foundation the RM theory"; could us why
> you are smarter than 30+ years of RDBMS theory?
>
> Please read at least one RDBMS book that will give you the basic
> definitions. You missed the part about how a column has to be atomic
> and scalar -- it should be in chapter 1, 2 or 3 depending how much
> history they do in the book.
>
> Oh, this also violates the **most fundamental principle" of a tiered
> architecture,which is that display is done in the front end and NEVER
> -- repeat NEVER-- in the back end.
>

20 Jan 2006 3:38 PM

Mark Williams

> Please read at least one RDBMS book that will give you the basic
> definitions. You missed the part about how a column has to be atomic
> and scalar -- it should be in chapter 1, 2 or 3 depending how much
> history they do in the book.
>

Actually, there is nothing in the original relational theory that prohibits
multi-valued attributes, like SQL 2005's xmldata datatype.

Every relation is, by defenition, in 1NF, even if it has multi-valued
attributes, as long as every attriute in every tuple has the correct data
type as defined in the heading of the relation.

20 Jan 2006 6:49 AM

Dejan Sarka

SQL 2005 User-Defined Aggregate function. SQL 2000 does not support them. in
SQL 2000, you can use a cursor to loop throught the rowset. See Joe Celko's
post - you should do it on the client side.

--
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message

Show quote

"T" <T@discussions.microsoft.com> wrote in message
news:5AD26F74-591D-4E50-93AE-9121AC0C27CC@microsoft.com...
>I have a table with the folowing few lines of data.
> 4460 Alexander, Julie A. 12 5 11/10/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/11/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/14/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/15/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/16/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/17/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/18/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/21/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/10/2005 0.60000002
> 3837 Anderson, Paula C. 10 5 11/11/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/14/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/15/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/17/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/18/2005 2.3
> 3837 Anderson, Paula C. 10 5 11/21/2005
> 4227 Andreoli, Elizabeth A. 10 2 11/10/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/11/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/14/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/15/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/16/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/17/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/18/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/21/2005 0.0
> 4040 Arens, Carolyn Sue 08 5 11/16/2005 3.8
> 4040 Arens, Carolyn Sue 08 5 11/21/2005 2.7
> 3611 Armstrong, Joe E. 01 1 11/15/2005
> 4408 Aronson, Daphne H. 01 3 11/10/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/11/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/14/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/11/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/18/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/11/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/16/2005 2.0
> 3665 Bridges, Tracy T. 01 1 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/10/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/11/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/14/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/15/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/16/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/17/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/21/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/10/2005 0.5
> 4071 Bunger, C. Kenneth 01 3 11/11/2005 2.3
> 4071 Bunger, C. Kenneth 01 3 11/14/2005 1.1
> 4071 Bunger, C. Kenneth 01 3 11/15/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/16/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/17/2005 2.0
> 4071 Bunger, C. Kenneth 01 3 11/18/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/21/2005 1.8
> 4428 Coats, Jeffrey A. 05 2 11/10/2005 0.0
>
> I simply wish to shrink to one line per name so that the dates(all per
> person) are in a singe column.
> example.
> 4071 Bunger, C. Kenneth 11/15/2005,11/11/2005,11/11/2005
> Any idea on the select given the way the data is stored ?

20 Jan 2006 7:42 AM

Tony Rogerson

If you are using SQL Server 2005 you can use a feature of the FOR XML...

select distinct type,
    (   select name + ', ' as [text()]
        from sys.objects s2
        where s2.type = s1.type
        order by s2.name
        for xml path( '' )
        ) as concatentated_name
from sys.objects s1

Gives....
D DF__spt_value__statu__436BFEE3,
IT queue_messages_1003150619, queue_messages_1035150733,
queue_messages_1067150847,

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"T" <T@discussions.microsoft.com> wrote in message
news:5AD26F74-591D-4E50-93AE-9121AC0C27CC@microsoft.com...
>I have a table with the folowing few lines of data.
> 4460 Alexander, Julie A. 12 5 11/10/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/11/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/14/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/15/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/16/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/17/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/18/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/21/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/10/2005 0.60000002
> 3837 Anderson, Paula C. 10 5 11/11/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/14/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/15/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/17/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/18/2005 2.3
> 3837 Anderson, Paula C. 10 5 11/21/2005
> 4227 Andreoli, Elizabeth A. 10 2 11/10/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/11/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/14/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/15/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/16/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/17/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/18/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/21/2005 0.0
> 4040 Arens, Carolyn Sue 08 5 11/16/2005 3.8
> 4040 Arens, Carolyn Sue 08 5 11/21/2005 2.7
> 3611 Armstrong, Joe E. 01 1 11/15/2005
> 4408 Aronson, Daphne H. 01 3 11/10/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/11/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/14/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/11/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/18/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/11/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/16/2005 2.0
> 3665 Bridges, Tracy T. 01 1 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/10/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/11/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/14/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/15/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/16/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/17/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/21/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/10/2005 0.5
> 4071 Bunger, C. Kenneth 01 3 11/11/2005 2.3
> 4071 Bunger, C. Kenneth 01 3 11/14/2005 1.1
> 4071 Bunger, C. Kenneth 01 3 11/15/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/16/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/17/2005 2.0
> 4071 Bunger, C. Kenneth 01 3 11/18/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/21/2005 1.8
> 4428 Coats, Jeffrey A. 05 2 11/10/2005 0.0
>
> I simply wish to shrink to one line per name so that the dates(all per
> person) are in a singe column.
> example.
> 4071 Bunger, C. Kenneth 11/15/2005,11/11/2005,11/11/2005
> Any idea on the select given the way the data is stored ?

20 Jan 2006 2:31 PM

Jim Underwood

What if the users name has changed and they have multiple names in this
table (maybe the ID stays the same)?
What about rows where the other column data is different for the same
ID/name (Maybe you are ignoring these columns).

Selecting only the values for the most recent dates might resolve both these
issues, assuming the fields are needed in your results at all.

Why are you trying to do this? Dependign on your answer, you may want to
have the client app handle this instead of the database.

Show quote

"T" <T@discussions.microsoft.com> wrote in message
news:5AD26F74-591D-4E50-93AE-9121AC0C27CC@microsoft.com...
> I have a table with the folowing few lines of data.
> 4460 Alexander, Julie A. 12 5 11/10/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/11/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/14/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/15/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/16/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/17/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/18/2005 0.0
> 4460 Alexander, Julie A. 12 5 11/21/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/10/2005 0.60000002
> 3837 Anderson, Paula C. 10 5 11/11/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/14/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/15/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/17/2005 0.0
> 3837 Anderson, Paula C. 10 5 11/18/2005 2.3
> 3837 Anderson, Paula C. 10 5 11/21/2005
> 4227 Andreoli, Elizabeth A. 10 2 11/10/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/11/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/14/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/15/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/16/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/17/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/18/2005 0.0
> 4227 Andreoli, Elizabeth A. 10 2 11/21/2005 0.0
> 4040 Arens, Carolyn Sue 08 5 11/16/2005 3.8
> 4040 Arens, Carolyn Sue 08 5 11/21/2005 2.7
> 3611 Armstrong, Joe E. 01 1 11/15/2005
> 4408 Aronson, Daphne H. 01 3 11/10/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/11/2005 0.0
> 4408 Aronson, Daphne H. 01 3 11/14/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/11/2005 0.0
> 3795 Barrett, Jennifer K. 02 3 11/18/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/11/2005 0.0
> 3665 Bridges, Tracy T. 01 1 11/16/2005 2.0
> 3665 Bridges, Tracy T. 01 1 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/10/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/11/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/14/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/15/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/16/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/17/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/18/2005 0.0
> 4410 Bullock, Michael C. 02 2 11/21/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/10/2005 0.5
> 4071 Bunger, C. Kenneth 01 3 11/11/2005 2.3
> 4071 Bunger, C. Kenneth 01 3 11/14/2005 1.1
> 4071 Bunger, C. Kenneth 01 3 11/15/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/16/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/17/2005 2.0
> 4071 Bunger, C. Kenneth 01 3 11/18/2005 0.0
> 4071 Bunger, C. Kenneth 01 3 11/21/2005 1.8
> 4428 Coats, Jeffrey A. 05 2 11/10/2005 0.0
>
> I simply wish to shrink to one line per name so that the dates(all per
> person) are in a singe column.
> example.
> 4071 Bunger, C. Kenneth 11/15/2005,11/11/2005,11/11/2005
> Any idea on the select given the way the data is stored ?