Select Two records in One Record

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 2008 Forums

Transact-SQL (2008)

Select Two records in One Record

Author

Topic

vjs2445
Starting Member

16 Posts

Posted - 2012-01-26 : 15:12:12

I am looking for help to build a query.

For one particular date condition and by each department:

If there are two records then combine those two records fields in one record as
Dept, Date, Value1, Value2, Value3, Value4

where Value1 and Value2 are first record
and Value 3 and Value4 are from secod record.

Note Value3 and Value4 are same column but 2nd row for same date condition.

In some case there will be always one row instead of two then I want to display Null for Value3 and Value4 such as

Dept, Date, Value1, Value2, Null, Null

Help would be appreciated.

Thanks and regards,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-26 : 15:50:28

What determines the "first" and "second" rows? By definition, data stored in a table have no order, so if you do want to define first and second in a deterministic manner, there has to be some other column that will help you do that. Assuming there is no such column(s), you can assign a "first" and "second" and then get your results as follows:

;WITH cte AS
(
	SELECT
		Dept,
		[Date],
		Value1,
		Value2,
		ROW_NUMBER() OVER(PARTITION BY Dept,[Date] ORDER BY (SELECT NULL)) AS RN
	FROM
		YourTable
)
SELECT
	a.Dept,
	a.[Date],
	a.Value1,
	a.Value2,
	b.Value1 AS Value3,
	b.Value2 AS Value4
FROM
	cte a
	LEFT JOIN cte b ON
		a.Dept = b.Dept AND a.[Date] = b.[Date] AND a.RN=1 AND b.RN=2;

vjs2445
Starting Member

16 Posts

Posted - 2012-01-26 : 16:26:17

Thanks Sunitabeck.

Here is my issue?

Each Dept has multiple records range from 1 to 10 or could be more 10. But I am only interested in the records whose date is greater than today and If there are one record then value3 and value4 will null (cuzz no second record exists) if there are two records with same date then output will be

Dept, Date, Value1, Value2, Value3, Value4

Hope I am able to explain.

Thanks for your help

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-26 : 16:29:04

post the DDL of the table(s)

Sample Data

and what the expect results are suppose to be

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

vjs2445
Starting Member

16 Posts

Posted - 2012-01-26 : 16:40:42

Following is the sample data as well the output I am looking for.

I need the only the record greater than today only:

Date Amount Type Description Amount_2 ID
3/15/2012 45 1 R 500 ID_1
3/15/2012 -35 2 P 500 ID_1
6/15/2012 NULL 1 P 500 ID_1
9/17/2012 450 2 R 500 ID_1
9/17/2012 NULL 2 P 500 ID_1
3/15/2012 45 1 R 500 ID_2
6/15/2012 NULL 1 P 500 ID_2
9/17/2012 450 2 R 500 ID_2
9/17/2012 NULL 2 P 500 ID_2

OutPut
Date Amount Type Description Amount_2 ID Date Amount Type Description Amount_2 ID
3/15/2012 45 1 R 500 ID_1 3/15/2012 -35 2 P 500 ID_1
3/15/2012 45 1 R 500 ID_2 Null Null Null Null Null Null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources