One-to-many in single - SQL Server Forums

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)

One-to-many in single

Author

Topic

kashyap81
Starting Member

9 Posts

Posted - 2009-02-09 : 00:58:57

Lets say we have a table structure such as this:

MainTable
---------
MainTableID
MainTableName

TableA
------
MainTableID
TableAID
TableAName

TableB
------
MainTableID
TableBID
TableBName

For every record in MainTable there are 3 corresponding records in TableA and TableB. I want a query that returns the data in this format:

MainTableID, MainTableName, TableAID1, TableAName1, TableAID2, TableAName2, TableAID3, TableAName3, TableBID1, TableBName1, TableBID2, TableBName2, TableBID3, TableBName3

Is this possible?

DiaTree.com
D.I.P. Pvt Ltd

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-09 : 01:10:18

Post ur sample data for tables,and Use dynamic cross tab

kashyap81
Starting Member

9 Posts

Posted - 2009-02-09 : 02:05:37

Ok... here's some sample data.

MainTable
---------
MainTableID MainTableName
1 Joe
2 Jack

TableA
------
TableAID MainTableID TableAName
1 1 Football
2 1 Baseball
3 1 Basketball
4 2 Tennis
5 2 Cricket
6 2 Soccer

TableB
------
TableBID MainTableID TableBName
1 1 Pasta
2 1 Pizza
3 1 Burger
4 2 Noodles
5 2 Burrito
6 2 Sandwich

I want the data to be displayed as follows:
MainTableID MainTableName TableAName1 TableAName2 TableAName3 TableBName1 TableBName2 TableBName3
1 Joe Football Baseball Basketball Pasta Pizza Burgers
2 Jack Tennis Cricket Soccer Noodles Burrito Sandwich

DiaTree.com
D.I.P. Pvt Ltd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 03:55:05

[code]SELECT m.MainTableName,
MAX(CASE WHEN a.Seq=1 THEN a.TableAName ELSE NULL END) AS TableAName1,
MAX(CASE WHEN a.Seq=1 THEN a.TableAID ELSE NULL END) AS TableAID1,
MAX(CASE WHEN a.Seq=2 THEN a.TableAName ELSE NULL END) AS TableAName2,
MAX(CASE WHEN a.Seq=2 THEN a.TableAID ELSE NULL END) AS TableAID2,
MAX(CASE WHEN a.Seq=3 THEN a.TableAName ELSE NULL END) AS TableAName3,
MAX(CASE WHEN a.Seq=3 THEN a.TableAID ELSE NULL END) AS TableAID3,
MAX(CASE WHEN b.Seq=1 THEN a.TableBName ELSE NULL END) AS TableBName1,
MAX(CASE WHEN b.Seq=1 THEN a.TableBID ELSE NULL END) AS TableBID1,
MAX(CASE WHEN b.Seq=2 THEN a.TableBName ELSE NULL END) AS TableBName2,
MAX(CASE WHEN b.Seq=2 THEN a.TableBID ELSE NULL END) AS TableBID2,
MAX(CASE WHEN b.Seq=3 THEN a.TableBName ELSE NULL END) AS TableAName3,
MAX(CASE WHEN b.Seq=3 THEN a.TableBID ELSE NULL END) AS TableBID3
FROM MainTable m
INNER JOIN
(SELECT (SELECT COUNT(*) FROM TableA WHERE MainTableID=t.MainTableID AND TableAID <=t.TableAID) AS Seq,
* FROM TableA t)a
ON a.MainTableID=m.MainTableID
INNER JOIN (SELECT (SELECT COUNT(*) FROM TableB WHERE MainTableID=t.MainTableID AND TableBID <=t.TableBID) AS Seq,
* FROM TableB t)b
ON b.MainTableID=m.MainTableID
GROUP BY m.MainTableName[/code]

kashyap81
Starting Member

9 Posts

Posted - 2009-02-09 : 05:41:02

I tried this and it worked!

Thank you very much Visakh.

DiaTree.com
D.I.P. Pvt Ltd

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 06:15:31

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources