Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to return multi rows into one row

Author  Topic 

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-08-28 : 16:04:58
I am trying to put rows with same date into one row. Do I need to insert the desired data into a temp table OR is there any other efficent way to do this?

Here is my sample data on table1:

COL1          COL2    
John     1/1/12 8:45
John     1/1/12 4:50
John     1/2/12 8:45
John     1/2/12 4:50
Alex     1/1/12 9:20
Alex     1/1/12 6:20
Alex     1/3/12 9:20
Alex     1/3/12 6:20

Desired Result
COL1          COL2             COL3  
John         1/1/12 8:45      1/1/12 4:50
John         1/2/12 8:45      1/2/12 4:50    
Alex         1/1/12 8:45      1/1/12 4:50   
Alex         1/2/12 8:45      1/2/12 4:50

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-08-28 : 17:21:13
I found a useful link that could apply to my situation:

http://dbaspot.com/sqlserver-programming/424135-how-combine-two-rows-into-one-row-same-id.html
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-29 : 14:37:28
quote:
Originally posted by emyk

I am trying to put rows with same date into one row. Do I need to insert the desired data into a temp table OR is there any other efficent way to do this?

Here is my sample data on table1:

COL1          COL2    
John     1/1/12 8:45
John     1/1/12 4:50
John     1/2/12 8:45
John     1/2/12 4:50
Alex     1/1/12 9:20
Alex     1/1/12 6:20
Alex     1/3/12 9:20
Alex     1/3/12 6:20

Desired Result
COL1          COL2             COL3  
John         1/1/12 8:45      1/1/12 4:50
John         1/2/12 8:45      1/2/12 4:50    
Alex         1/1/12 8:45      1/1/12 4:50   
Alex         1/2/12 8:45      1/2/12 4:50



is it always two rows for a day? what if it has more rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Andy Hyslop
Starting Member

14 Posts
Posted - 2012-08-30 : 10:35:25
Don't know if this will work for you, probably much better ways of doing it but am stuck for time!

Plus I don't think your test data and required outputs are correct 04:50 is before 08:45 and I don't fully understand why Alex has two rows with 8:45 and 4:50 as his rows contain no such times?


BEGIN TRAN

CREATE TABLE #TEMP

(COL1 CHAR(4),COL2 DATETIME)

INSERT INTO #TEMP   
SELECT 'John'   ,  '1/1/12 8:45' UNION ALL
SELECT 'John'   , '1/1/12 4:50' UNION ALL
SELECT 'John'   ,  '1/2/12 8:45' UNION ALL
SELECT 'John'   ,  '1/2/12 4:50' UNION ALL
SELECT 'Alex'   ,  '1/1/12 9:20' UNION ALL
SELECT 'Alex'   ,  '1/1/12 6:20' UNION ALL
SELECT 'Alex'   ,  '1/3/12 9:20' UNION ALL
SELECT 'Alex'   ,  '1/3/12 6:20'


SELECT  *
FROM    #TEMP
;

WITH CTEA
AS
 
(	SELECT 
	*
	,ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2 ASC) AS RowNum
	FROM
	#TEMP
) 
SELECT  *
FROM    CTEA
CROSS APPLY ( SELECT * FROM CTEA AS B WHERE CTEA.RowNum = B.RowNum - 1 AND CTEA.COL1 = B.COL1 AND DATEDIFF(DAY,CTEA.COL2,B.COL2) < 1) AS B



ROLLBACK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-30 : 10:55:44
the fact is that timepart is not represented correctly. either it has to be in 24 hr format or it should have AM/PM

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Mike Jackson
Starting Member

37 Posts
Posted - 2012-08-30 : 16:35:54
tried to post this yesterday but it did not go thru.


select col1, min(col2), max(col2), count(col1)
from table1
group by col1, year(col2), month(col2), day(col2)
order by year(col2) desc, month(col2), day(col2)

Simi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-30 : 17:10:25
quote:
Originally posted by Mike Jackson

tried to post this yesterday but it did not go thru.


select col1, min(col2), max(col2), count(col1)
from table1
group by col1, year(col2), month(col2), day(col2)
order by year(col2) desc, month(col2), day(col2)

Simi

insteading of grouping separately on year,month,day isnt it enough to group on date value without considering time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-08-30 : 17:31:09
I left out the AM/PM part. But thank you all for providing with a solution. I think Andy's solution best fits my need.

thanks
Alex
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-30 : 17:48:21
quote:
Originally posted by emyk

I left out the AM/PM part. But thank you all for providing with a solution. I think Andy's solution best fits my need.

thanks
Alex

ok if you've date properly represented it will do the trick for you.

one small change i would do is

;WITH CTEA
AS
 
(	SELECT 
	*
	,ROW_NUMBER() OVER (PARTITION BY COL1,DATEADD(dd,DATEDIFF(dd,0,COL2),0) ORDER BY COL2 ASC) AS RowNum
	FROM
	#TEMP
) 
SELECT  A.COL1,B.COL2,A.COL2
FROM    CTEA AS A
INNER JOIN CTEA AS B 
ON  B.RowNum = A.RowNum - 1 
AND A.COL1 = B.COL1 
AND DATEDIFF(dd,0,A.COL2) = DATEDIFF(dd,0,B.COL2)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-08-30 : 18:14:03
I changed the datediff function to calculate hours between a given date so that I can put date and times together that falls under a given hours, and this seems to work fine.

Now, I want to add one more criteria which is to add a new row if a date and time that does not fit the given critria.

Here is an updated Andy's script:

<CODE>
CREATE TABLE #TEMP

(COL1 CHAR(4),COL2 DATETIME)

INSERT INTO #TEMP
SELECT 'John' , '12/31/11 10:45' UNION ALL --- I NEED TO DISPLAY THIS LINE BY ITSELF
SELECT 'John' , '1/1/12 8:45' UNION ALL
SELECT 'John' , '1/1/12 16:50' UNION ALL
SELECT 'John' , '1/2/12 8:45' UNION ALL
SELECT 'John' , '1/2/12 16:50' UNION ALL
SELECT 'Alex' , '1/1/12 23:20' UNION ALL
SELECT 'Alex' , '1/2/12 07:20' UNION ALL
SELECT 'Alex' , '1/3/12 9:20' UNION ALL
SELECT 'Alex' , '1/3/12 18:20'


SELECT *
FROM #TEMP
;

WITH CTEA
AS

( SELECT
*
,ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2 ASC) AS RowNum
FROM
#TEMP
)
SELECT *
FROM CTEA
CROSS APPLY ( SELECT * FROM CTEA AS B WHERE CTEA.RowNum = B.RowNum - 1 AND CTEA.COL1 = B.COL1 AND DATEDIFF(hour,CTEA.COL2,B.COL2) <= 10) AS B
</CODE>
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-30 : 21:22:01 


;WITH CTEA
AS
 
(	SELECT 
	*
	,ROW_NUMBER() OVER (PARTITION BY COL1,DATEADD(dd,DATEDIFF(dd,0,COL2),0) ORDER BY COL2 ASC) AS RowNum
	FROM
	#TEMP
) 
SELECT  A.COL1,B.COL2,A.COL2
FROM    CTEA AS A
LEFT JOIN CTEA AS B 
ON  B.RowNum = A.RowNum - 1 
AND A.COL1 = B.COL1 
AND DATEDIFF(dd,0,A.COL2) = DATEDIFF(dd,0,B.COL2)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-08-31 : 11:01:28
visakh16 - Here is what I get when I run the above script that was posted on 08/30/2012 : 18:14:03
The first line is not being tracked because I hard coded to give me times <=10 hours in one raw.
What I need to do is that if i came across with a date and time ouside the 10 hours, I need to insert the date and time as follows:

COL1 COL2                     RowNum  COL1  COL2   RowNum
---- -------                  -------- ----- ------- ---- 
JOHN 2011-12-31 10:45:00.0001  NULL     NULL  NULL   NULL


Here is the results from the current run:


COL1 COL2
---- -----------------------
John 2011-12-31 10:45:00.000 --THIS IS THE LINE THAT I NEED TO CATCH ON THE FINAL RESLUT
John 2012-01-01 08:45:00.000
John 2012-01-01 16:50:00.000
John 2012-01-02 08:45:00.000
John 2012-01-02 16:50:00.000
Alex 2012-01-01 23:20:00.000
Alex 2012-01-02 07:20:00.000
Alex 2012-01-03 09:20:00.000
Alex 2012-01-03 18:20:00.000

Here is desired result
COL1 COL2                    RowNum               COL1 COL2                    RowNum
---- ----------------------- -------------------- ---- --------

Alex 2012-01-01 23:20:00.000 1      Alex 2012-01-02 07:20:00.000 2
Alex 2012-01-03 09:20:00.000 3         Alex 2012-01-03 18:20:00.000 4
JOHN 2011-12-31 10:45:00.0001  NULL     NULL  NULL   NULL
John 2012-01-01 08:45:00.000 2                    John 2012-01-01 16:50:00.000 3
John 2012-01-02 08:45:00.000 4                    John 2012-01-02 16:50:00.000 5
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-08-31 : 11:20:18
change cross apply to outer apply

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-09-03 : 20:34:00
when applying the outer join, It is listing records twice. Some how I need to remove the duplicate date time rows listed below. I highlited the only valid null row.


COL1 COL2                    RowNum               COL1 COL2                    RowNum

---- ----------------------- -------------------- ---- ----------------------- --------------------
Alex 2012-01-01 23:20:00.000 1                    Alex 2012-01-02 07:20:00.000 2
Alex 2012-01-02 07:20:00.000 2                    NULL NULL                    NULL
Alex 2012-01-03 09:20:00.000 3                    Alex 2012-01-03 18:20:00.000 4
Alex 2012-01-03 18:20:00.000 4                    NULL NULL                    NULL
John 2011-12-31 10:45:00.000 1                    NULL NULL                    NULL
John 2012-01-01 08:45:00.000 2                    John 2012-01-01 16:50:00.000 3
John 2012-01-01 16:50:00.000 3                    NULL NULL                    NULL
John 2012-01-02 08:45:00.000 4                    John 2012-01-02 16:50:00.000 5
John 2012-01-02 16:50:00.000 5                    NULL NULL                    NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-03 : 20:52:38
this should do trick for you


;WITH CTEA
AS
 
(	SELECT 
	*
	,ROW_NUMBER() OVER (PARTITION BY COL1,DATEADD(dd,DATEDIFF(dd,0,COL2),0) ORDER BY COL2 ASC) AS RowNum
	FROM
	#TEMP
) 
SELECT  COL1,
MAX(CASE WHEN RowNum=1 THEN COL2 END) AS Start,
MAX(CASE WHEN RowNum=2 THEN COL2 END) AS End
FROM    CTEA 
GROUP BY COL1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-09-03 : 20:57:12
I am getting syntax error:
"Incorrect syntax near the keyword 'End'."
here is the coplete code:

CREATE TABLE #TEMP

(COL1 CHAR(4),COL2 DATETIME)

INSERT INTO #TEMP
SELECT 'John' , '12/31/11 10:45' UNION ALL --- I NEED TO DISPLAY THIS LINE BY ITSELF
SELECT 'John' , '1/1/12 8:45' UNION ALL
SELECT 'John' , '1/1/12 16:50' UNION ALL
SELECT 'John' , '1/2/12 8:45' UNION ALL
SELECT 'John' , '1/2/12 16:50' UNION ALL
SELECT 'Alex' , '1/1/12 23:20' UNION ALL
SELECT 'Alex' , '1/2/12 07:20' UNION ALL
SELECT 'Alex' , '1/3/12 9:20' UNION ALL
SELECT 'Alex' , '1/3/12 18:20'


SELECT *
FROM #TEMP
;
WITH CTEA
AS
 
(	SELECT 
	*
	,ROW_NUMBER() OVER (PARTITION BY COL1,DATEADD(dd,DATEDIFF(dd,0,COL2),0) ORDER BY COL2 ASC) AS RowNum
	FROM
	#TEMP
) 
SELECT  COL1,
MAX(CASE WHEN RowNum=1 THEN COL2 END) AS Start,
MAX(CASE WHEN RowNum=2 THEN COL2 END) AS End
FROM    CTEA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-03 : 20:59:12
quote:
Originally posted by emyk

I am getting syntax error:
"Incorrect syntax near the keyword 'End'."
here is the coplete code:

CREATE TABLE #TEMP

(COL1 CHAR(4),COL2 DATETIME)

INSERT INTO #TEMP
SELECT 'John' , '12/31/11 10:45' UNION ALL --- I NEED TO DISPLAY THIS LINE BY ITSELF
SELECT 'John' , '1/1/12 8:45' UNION ALL
SELECT 'John' , '1/1/12 16:50' UNION ALL
SELECT 'John' , '1/2/12 8:45' UNION ALL
SELECT 'John' , '1/2/12 16:50' UNION ALL
SELECT 'Alex' , '1/1/12 23:20' UNION ALL
SELECT 'Alex' , '1/2/12 07:20' UNION ALL
SELECT 'Alex' , '1/3/12 9:20' UNION ALL
SELECT 'Alex' , '1/3/12 18:20'


SELECT *
FROM #TEMP
;
WITH CTEA
AS
 
(	SELECT 
	*
	,ROW_NUMBER() OVER (PARTITION BY COL1,DATEADD(dd,DATEDIFF(dd,0,COL2),0) ORDER BY COL2 ASC) AS RowNum
	FROM
	#TEMP
) 
SELECT  COL1,
MAX(CASE WHEN RowNum=1 THEN COL2 END) AS Start,
MAX(CASE WHEN RowNum=2 THEN COL2 END) AS [End]
FROM    CTEA 
 GROUP BY COL1


it turns out to be a reserved word
put [] around it
also missing GROUP BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-09-03 : 21:07:14
I appriciate your help on this. Now I am getting "Warning: Null value is eliminated by an aggregate or other SET operation"


(9 row(s) affected)
COL1 COL2
---- -----------------------
John 2011-12-31 10:45:00.000
John 2012-01-01 08:45:00.000
John 2012-01-01 16:50:00.000
John 2012-01-02 08:45:00.000
John 2012-01-02 16:50:00.000
Alex 2012-01-01 23:20:00.000
Alex 2012-01-02 07:20:00.000
Alex 2012-01-03 09:20:00.000
Alex 2012-01-03 18:20:00.000

(9 row(s) affected)

COL1 Start                   End
---- ----------------------- -----------------------
Alex 2012-01-03 09:20:00.000 2012-01-03 18:20:00.000
John 2012-01-02 08:45:00.000 2012-01-02 16:50:00.000
Warning: Null value is eliminated by an aggregate or other SET operation.

(2 row(s) affected)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-03 : 21:33:36
quote:
Originally posted by emyk

I appriciate your help on this. Now I am getting "Warning: Null value is eliminated by an aggregate or other SET operation"


(9 row(s) affected)
COL1 COL2
---- -----------------------
John 2011-12-31 10:45:00.000
John 2012-01-01 08:45:00.000
John 2012-01-01 16:50:00.000
John 2012-01-02 08:45:00.000
John 2012-01-02 16:50:00.000
Alex 2012-01-01 23:20:00.000
Alex 2012-01-02 07:20:00.000
Alex 2012-01-03 09:20:00.000
Alex 2012-01-03 18:20:00.000

(9 row(s) affected)

COL1 Start                   End
---- ----------------------- -----------------------
Alex 2012-01-03 09:20:00.000 2012-01-03 18:20:00.000
John 2012-01-02 08:45:00.000 2012-01-02 16:50:00.000
Warning: Null value is eliminated by an aggregate or other SET operation.

(2 row(s) affected)


thats just a warning and doesnt cause any issue
if you want you can turn it off from popping using

SET ANSI_WARNINGS OFF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emyk
Yak Posting Veteran

57 Posts
Posted - 2012-09-05 : 17:38:32
I used the outer join (visakh16 recommendation), and these returns all the rows I need, the only issue is that it returns duplicate values. 


SET ANSI_WARNINGS OFF

CREATE TABLE #TEMP

(COL1 CHAR(4),COL2 DATETIME)

INSERT INTO #TEMP
SELECT 'John' , '12/31/11 10:45' UNION ALL
SELECT 'John' , '1/1/12 8:45' UNION ALL
SELECT 'John' , '1/1/12 16:50' UNION ALL
SELECT 'John' , '1/2/12 8:45' UNION ALL
SELECT 'John' , '1/2/12 16:50' UNION ALL
SELECT 'Alex' , '1/1/12 23:20' UNION ALL
SELECT 'Alex' , '1/2/12 07:20' UNION ALL
SELECT 'Alex' , '1/3/12 9:20' UNION ALL
SELECT 'Alex' , '1/3/12 18:20'


--SELECT *
--FROM #TEMP
;

WITH CTEA
AS

( SELECT 
*
,ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2 ASC) AS RowNum
FROM
#TEMP
) 
SELECT CTEA.COL1 NAME, CTEA.COL2  START,B.COL2 [END]
FROM CTEA
outer APPLY ( SELECT * FROM CTEA AS B 
WHERE CTEA.RowNum = B.RowNum - 1 
AND CTEA.COL1 = B.COL1 
AND (DATEDIFF(hour,CTEA.COL2,B.COL2) <= 10)) AS B



Here is the output. How do I exclude a value from the start column that already exists under the end column?
The highlighted one's are the values that need to be excluded.

NAME START                   END
---- ----------------------- -----------------------
Alex 2012-01-01 23:20:00.000 2012-01-02 07:20:00.000
Alex 2012-01-02 07:20:00.000 NULL 
Alex 2012-01-03 09:20:00.000 2012-01-03 18:20:00.000
Alex 2012-01-03 18:20:00.000 NULL
John 2011-12-31 10:45:00.000 NULL
John 2012-01-01 08:45:00.000 2012-01-01 16:50:00.000
John  2012-01-01 16:50:00.000 NULL
John 2012-01-02 08:45:00.000 2012-01-02 16:50:00.000
John 2012-01-02 16:50:00.000  NULL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-09-05 : 22:09:30
hmm...what happened to my last suggestion using GROUP BY?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site