Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Query

Query

Rate Topic

Display Mode

Topic Options

Author

Message

Prakash-485822

Posted Sunday, April 27, 2008 10:58 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:46 AM
Points: 26, Visits: 110

Dear All,
I have 2 table TableA with columns(FormId,Name,Desc)
TableB with Columns (FormId,ContainerType,MainFormId,Desc)

TableA
FormId Name Desc
1 A A
2 B B

TableB

FormId ContainerType MainFormId Desc
1 2SG 1 AA
2 4SG 1 AA
3 20R 1 AA
4 2SG 1 AA
5 20T 1 AA
6 40T 2 AA
7 40T 2 AA
8 4SG 2 AA
9 20R 2 AA
10 40T 2 AA
11 20T 2 AA
12 2SG 2 AA

i want the output like
1 A 2SG,2 20T,1 4SG,1 20R ,1 40T,1 A AA
2 b 2SG,1 20T,1 4SG,1 20R ,1 40T,3 A AA

Please help me to build a query for this

Post #491182

ChiragNS

Posted Sunday, April 27, 2008 11:55 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

Pls explain the question clearly

"Keep Trying"

Post #491190

Anipaul

Posted Monday, April 28, 2008 12:16 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 4,787, Visits: 1,335

The output you wanted is very cumbersome. Think of a matrix type output or Pivot table like output. :)

Post #491199

Prakash-485822

Posted Monday, April 28, 2008 3:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:46 AM
Points: 26, Visits: 110

Actuall i have 3 table StuffTbl,ContainerTbl,ContainerTypeTbl,
StuffTbl contians FormId,CreatedBy,POD Columns.
ContainerTbl Contains FormId,ContainerTypeId(Ref key ContainerTypeTbl),StuffId(Ref key StuffTbl),Remarks

ContainerTypeTbl contains continerid,Type.

StuffTbl data
FormId CreatedBy POD
1 Rocky INT
2 James RTW

ContinerTbl
FormId ContainerTypeId StuffID Remarks
1 101 1
2 102 1
3 107 1
4 118 1
5 101 2
6 102 2
7 106 2
8 112 2
9 118 2
10 117 2

ContainerTypeTbl
ContainerId Type
101 20R
102 2FR
103 2HG
104 2HT
105 20T
106 2SG
107 40R
108 45F
109 4FR
110 4HG
111 40T
112 4SG
113 AIR
114 BB
115 FR2
116 FR4
117 ISO
118 LCL

I want the Output in this formate

2CType means the in Type which starts with 2
4CType means the in Type which starts with 4
FormId POD 2CType 4CType LCL AIR BB FR2 FR4 ISO Remarks
1 INT 2 1 1 0 0 0 0 0
2 RTW 3 1 1 0 0 0 0 1

Post #491265

Sergiy

Posted Monday, April 28, 2008 6:13 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184

Check topic "Cross-Tab Reports" in BOL.
It describes how to build queries exactly like yours.
Good examples included.

Post #491319

« Prev Topic | Next Topic »

Permissions