is there a way to show this SQL query on 1 line?

Question

I'm looking to show on 1 line my clients name and all of their check dates for the month of January in 2014. Some clients may have 5 check dates in the month while other may only have 1 or 3. Is there a way to make this happen? Using this SQL query:

SELECT CC.co, checkDate FROM CCalendar CC
    INNER JOIN CInfo CI on CC.co = CI.co 
WHERE checkDate BETWEEN '01/01/2014' AND '01/31/2014' 
    AND CI.endDate IS NULL AND CI.status IN ('LIVE', 'CONVERSION')

The results currently look like this:

co | checkDate
---+------------
co | 01/03/2014
co | 01/14/2014
co | 01/17/2014
co | 01/24/2014
co | 01/24/2014

and what I want it to look like is this

Co | checkdate1| checkdate2| checkdate3| checkdate4| checkdate5|
co | 01/03/2014| 01/14/2014| 01/17/2014| 01/24/2014| 01/24/2014|

Which check date do you want to show, if there *are* multiple values? The system doesn't *guess* — Damien_The_Unbeliever, Oct 23 '13 at 17:58
I would like to show all check dates within the month. some clients (very few) will have checkdates on the same date in cases where mngmt gets paid separately from other employees. — BSanders, Oct 23 '13 at 18:02
can you edit your question and show your expected results then? Your explanation isn't helping me — Damien_The_Unbeliever, Oct 23 '13 at 18:04

score 2 · Answer 1 · edited May 23 '17 at 12:10

You're what you're looking for is a function similar to MySql's group_concat function, only in SQL Server instead.

This is probably a bad idea to do this in your query... instead, you should handle this in your UI / front-end, and build up your comma separated checkDate column there instead.

However, if you're still wanting to do this in your query, this question's answers show how this can be done in SQL Server.

score 0 · Answer 2 · answered Oct 23 '13 at 18:04

This will use the XML features of sql server to make a comma separated list of dates

SELECT CC.co,
  stuff((
    SELECT ',' + CAST(checkDate AS varchar(MAX))
    FROM CInfo CI
    WHERE CI.co = CC.co
     AND checkDate BETWEEN '01/01/2014' AND '01/31/2014' 
     AND CI.endDate IS NULL AND CI.status IN ('LIVE', 'CONVERSION') 
    for xml path('')
  ),1,1,'') as CheckDateList

Rachid · Answer 3 · 2013-10-24T13:27:42.637

0

Try using CASE .. WHEN .. THEN .. ELSE .. END' syntax in SQL.

SELECT CC.co,

(case when ROW_NUMBER() OVER (ORDER BY CC.co) = 1 then checkDate end) as [checkDate1],

(case when ROW_NUMBER() OVER (ORDER BY CC.co) = 2 then checkDate end) as [checkDate2],

(case when ROW_NUMBER() OVER (ORDER BY CC.co) = 3 then checkDate end) as [checkDate3],

(case when ROW_NUMBER() OVER (ORDER BY CC.co) = 4 then checkDate end) as [checkDate4],

(case when ROW_NUMBER() OVER (ORDER BY CC.co) = 5 then checkDate end) as [checkDate5]

FROM CCalendar CC
    INNER JOIN CInfo CI on CC.co = CI.co 
WHERE checkDate BETWEEN '01/01/2014' AND '01/31/2014' 
    AND CI.endDate IS NULL AND CI.status IN ('LIVE', 'CONVERSION')

edited Oct 24 '13 at 13:27

answered Oct 23 '13 at 18:16

Rachid

812
6
7

this looks like it will be what i need but i get this error: Invalid column name 'RowNum'. – BSanders Oct 23 '13 at 19:13
How About replacing RowNum with ROW_NUMBER() OVER (ORDER BY CC.co). See updated SQL – Rachid Oct 23 '13 at 19:56
i get this error Windowed functions cannot be used in the context of another windowed function or aggregate. not sure why though. – BSanders Oct 24 '13 at 12:21
I think I know why you're getting this error message. if you you look at this [article](http://www.sqlerror.sqlserver-training.com/tag/windowed-functions-cannot-be-used-in-the-context-of-another-windowed-function-or-aggregate/) it explains why. I updated the query to reflect the solution. However, I highly recommend my second solution (above or/ below) cause I tested it and it worked. Couldn't upload a picture that shows the result. – Rachid Oct 24 '13 at 13:26

Rachid · Accepted Answer · 2013-10-24T17:56:05.207

0

OK, What you need to use here is a combination of both PIVOT and ROW_NUMBER.

Your statment should be close to this:

SELECT co, [1] as checkDate1, [2] checkDate2, [3] checkDate3, [4] checkDate4, [5] checkDate5 FROM
(
SELECT CC.co, 
ROW_NUMBER() OVER (PARTITION BY CC.co ORDER BY CC.co) AS CheckDateNum, checkDate 
FROM CCalendar CC
    INNER JOIN CInfo CI on CC.co = CI.co 
WHERE checkDate BETWEEN '01/01/2014' AND '01/31/2014' 
    AND CI.endDate IS NULL AND CI.status IN ('LIVE', 'CONVERSION')
) AS T1

PIVOT (MAX(checkDate) for CheckDateNum in ([1],[2],[3],[4],[5])) as checkDate

Example:

enter image description here

edited Oct 24 '13 at 17:56

answered Oct 23 '13 at 20:56

Rachid

812
6
7

Please check this [solution](http://stackoverflow.com/questions/5624262/understanding-compatibility-level-in-sql-server) for the error. I've tested this on SQL Server 2008 R2. You might have to alter your compatibility level like this: `ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 100` – Rachid Oct 24 '13 at 13:32
I got this working!!! Thank you! I'm trying to change the format of the date time to a standard us date IE 10/29/2013 and i keep getting errors. any clue where I would put that? – BSanders Oct 29 '13 at 16:16

is there a way to show this SQL query on 1 line?

4 Answers4