How to get results in one row from multiple 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

General SQL Server Forums

New to SQL Server Programming

How to get results in one row from multiple record

Author

Topic

Niki
Yak Posting Veteran

51 Posts

Posted - 2013-07-19 : 18:06:05

I have following situation -

DECLARE @table1 TABLE
(
Stuid INT NOT NULL,
schoolcd varchar(10),
enterDate Datetime,
entercd char(10)
)

INSERT @Student VALUES
(4648, ‘20130515’, ‘892’,’30’),
(4648, ‘20130103’, ‘764,’10’)

SELECT STATEMENT will give me following -

Stuid schoolcd entercd enterDate
4648 892 30 05/15/2013
4648 764 10 01/03/2013

Desired output is one row for two or more records for same stuid
4648 892 30 05/15/2013 764 10 01/03/2013 xxx xx xx/xx/xxxx

how can I accomplish that?
Thanks,

Niki

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-19 : 20:52:32

Here is a way:
[CODE]
DECLARE @Student TABLE
(
Stuid INT NOT NULL,
schoolcd varchar(10),
enterDate Datetime,
entercd char(10)
);

INSERT @Student(Stuid, enterDate, schoolcd, entercd) VALUES
(4648, '2013-05-15', '892','30'),
(4648, '2013-01-03', '764','10');

;WITH CTE AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY Stuid Order by enterDate) as RN FROM @student)
SELECT T1.Stuid, T1.enterDate, T1.schoolcd, T1.entercd, T2.enterDate, T2.schoolcd, T2.entercd
FROM CTE T1 LEFT JOIN CTE T2 ON T1.Stuid = T2.Stuid and T1.RN = T2.RN-1 WHERE t1.RN = 1

[/CODE]

Niki
Yak Posting Veteran

51 Posts

Posted - 2013-07-22 : 11:56:21

Thank you, MuMu88!

Niki

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 11:58:21

Not for two OR MORE rows...

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-22 : 12:05:01

[code]DECLARE @Student TABLE
(
Stuid INT NOT NULL,
schoolcd varchar(10),
entercd char(10),
enterDate Datetime
)

INSERT @Student VALUES
(4648, '892', '30', '20130515'),
(4648, '764', '10', '20130103');

-- SwePeso
SELECT d.StuID,
f.Data
FROM (
SELECT DISTINCT StuID
FROM @Student
) AS d
CROSS APPLY (
SELECT ' ' + RTRIM(s.SchoolCD) + ' ' + RTRIM(s.EnterCD) + ' ' + CONVERT(VARCHAR(10), s.EnterDate, 101)
FROM @Student AS s
WHERE s.StuID = d.StuID
ORDER BY s.SchoolCD
FOR XML PATH('')
) AS f(Data)
ORDER BY d.StuID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-22 : 12:24:11

quote:

Originally posted by SwePeso

DECLARE @Student TABLE
(
	Stuid INT NOT NULL, 
	schoolcd varchar(10),
	entercd char(10),
	enterDate Datetime
)

INSERT @Student VALUES
(4648, '892', '30', '20130515'),
(4648, '764', '10', '20130103');

-- SwePeso
SELECT		d.StuID,
		f.Data
FROM		(
			SELECT DISTINCT	StuID
			FROM		@Student
		) AS d
CROSS APPLY	(
			SELECT		' ' + RTRIM(s.SchoolCD) + ' ' + RTRIM(s.EnterCD) + ' ' + CONVERT(VARCHAR(10), s.EnterDate, 101)
			FROM		@Student AS s
			WHERE		s.StuID = d.StuID
			ORDER BY	s.SchoolCD
			FOR XML		PATH('')
		) AS f(Data)
ORDER BY	d.StuID;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Depends on whether OP wants values in single or separate columns.
If former, above is fine

If latter dynamic sql may be what Op is after

as in

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources