How to display row by row data in coloumn format.

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

SQL Server Development (2000)

How to display row by row data in coloumn format.

Author

Topic

Jabez
Starting Member

19 Posts

Posted - 2007-03-07 : 00:34:51

Hi folks,

Please help me out in writing a query.

Question

Table 1 : Employee
Table 2 : Magazines
Table 3 : EmployeeMagazines

Table 1 : Employee

EmpId EmpName
----- -------
1 Sailesh
2 Amit

Table 2: Magazines

Mg_ID Mg_Name
----- -------
1 PC-World
2 Digit
3 Linux for You

Table 3: EmployeeMagazines

EmpId Mg_ID
----- -----
1 1
1 2
2 3

Now, How do I write a query to get the results as shown below:

EmpName Magazine1 Magazine2
------- --------- ---------
Sailesh PC-World Digit
Amit Linux for You

Thanks,
Regards
Jabez

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-07 : 01:11:17

SELECT e.EmpName, MIN(m.Mg_Name) AS Magazine1, NULLIF(MAX(m.Mg_Name), MIN(m.Mg_Name)) AS Magazine2
FROM Employees AS e
INNER JOIN EmployeeMagazines AS em ON em.EmpID = e.EmpID
INNER JOIN Magazines AS m ON m.Mg_ID = em.Mg_ID
GROUP BY e.EmpID, e.EmpName

Peter Larsson
Helsingborg, Sweden

Jabez
Starting Member

19 Posts

Posted - 2007-03-08 : 05:19:04

Thanks Peter for the reply.

It shows the data as

EmpName Magazine1 Magazine2
------- --------- ---------
sailesh Digit PC-World
amit LFY NULL

But I need the data as
EmpName Magazine1 Magazine2
------- --------- ---------
sailesh PC-World Digit
amit LFY NULL

Please help me.

Thanking you in anticipation.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 05:23:24

Just replace MIN vs MAX and MAX vs MIN...

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 05:25:01

just change MAX to MIN, MIN to MAX


SELECT e.EmpName, MAX(m.Mg_Name) AS Magazine1, NULLIF(MIN(m.Mg_Name), MAX(m.Mg_Name)) AS Magazine2
FROM Employees AS e
INNER JOIN EmployeeMagazines AS em ON em.EmpID = e.EmpID
INNER JOIN Magazines AS m ON m.Mg_ID = em.Mg_ID
GROUP BY e.EmpID, e.EmpName

refresh refresh refresh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 05:48:33

Reading this post again, makes me think he wants the magazines in Mag_ID order...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 05:55:45

[code]-- Prepare sample data
DECLARE @Employees TABLE (EmpId INT, EmpName VARCHAR(20))

INSERT @Employees
SELECT 1, 'Sailesh' UNION ALL
SELECT 2, 'Amit'

DECLARE @Magazines TABLE (Mg_ID INT, Mg_Name VARCHAR(20))

INSERT @Magazines
SELECT 1, 'PC-World' UNION ALL
SELECT 2, 'Digit' UNION ALL
SELECT 3, 'Linux for You'

DECLARE @EmployeesMagazines TABLE (EmpId INT, Mg_ID INT)

INSERT @EmployeesMagazines
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 3

-- Show the expected result
SELECT e.EmpName AS EmpName,
m1.Mg_Name AS Magazine1,
m2.Mg_Name AS Magazine2
FROM (
SELECT e.EmpID,
MIN(em.Mg_ID) AS MinMagID,
NULLIF(MAX(em.Mg_ID), MIN(em.Mg_ID)) AS MaxMagID
FROM @Employees AS e
INNER JOIN @EmployeesMagazines AS em ON em.EmpID = e.EmpID
GROUP BY e.EmpID
) AS x
INNER JOIN @Employees AS e ON e.EmpID = x.EmpID
LEFT JOIN @Magazines AS m1 ON m1.Mg_ID = x.MinMagID
LEFT JOIN @Magazines AS m2 ON m2.Mg_ID = x.MaxMagID
ORDER BY x.EmpID[/code]

Peter Larsson
Helsingborg, Sweden

Jabez
Starting Member

19 Posts

Posted - 2007-03-10 : 06:52:50

Thanks Peter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources