need help on tSQL - 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 2005 Forums

Transact-SQL (2005)

need help on tSQL

Author

Topic

sks198117
Starting Member

46 Posts

Posted - 2010-12-03 : 03:23:04

Hi ,
I have 2 tables like following

and need output as mentioned
------------------------
tbl1
itemid name
1 a
2 b
3 c
4 d
tbl2
itemid name
1 a
2 b
3 c
4 d
1 x
2 y
3 z
4 m
1 p
2 q
3 r
4 s

output
1 a(a:x:p)
2 b(b:y:q)
3 c(c:z:r)
4 d(d:m:s)
----------------------
Please anyone help me what will be best way to do that.

Thanks

---Thanks---
Santosh

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-03 : 03:59:53

Try This -


;WITH C AS 
(
SELECT * FROM tbl1
)

SELECT DISTINCT ItemID, 
Name + '(' + STUFF((SELECT ':' + Name FROM tbl2 WHERE ItemID = C.ItemID
FOR XML PATH('')),1,1,'')  + ')' AS Name
FROM C

Vaibhav T

If I cant go back, I want to go fast...

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-03 : 04:30:18

Hi,

Another Approach

declare @tbl1 table
(
itemid tinyint,
name char(1)
)

insert into @tbl1(itemid,name)
(
select itemid,name from
(
values
(1,'a')
,(2,'b')
,(3,'c')
,(4,'d')
)t(itemid,name)
)

declare @tbl2 table
(
itemid tinyint,
name char(1)
)

insert into @tbl2(itemid,name)
(
select itemid,name from
(
values
(1,'a')
,(2,'b')
,(3,'c')
,(4,'d')
,(1,'x')
,(2,'y')
,(3,'z')
,(4,'m')
,(1,'p')
,(2,'q')
,(3,'r')
,(4,'s')
)t(itemid,name)
)

select *
from
(select itemid,name from @tbl1)
t
cross apply
(
select [s]=t.name + '(' + stuff(
(
select t1.name + ':'
from
@tbl2 t1
where t.itemid = t1.itemid
for xml path('')
),1,0,'') +')'
)t1

/*output
1 a(a:x:p)
2 b(b:y:q)
3 c(c:z:r)
4 d(d:m:s)*/

Iam a slow walker but i never walk back

sks198117
Starting Member

46 Posts

Posted - 2010-12-03 : 04:42:14

thanks guys for your promt response..i will check with my actual data and will tell you if i am getting desired output or not.

Thanks alot for quick response :)

---Thanks---
Santosh

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-03 : 06:12:57

Welcome

Vaibhav T

If I cant go back, I want to go fast...

sks198117
Starting Member

46 Posts

Posted - 2010-12-03 : 06:16:03

I am stuck with my original data
i am using below query as suggested
----

SELECT *
FROM
(SELECT top 10 ITEMID,CM_NAME from PMS.UPF_ACTIVITY ) t
CROSS APPLY

(
SELECT [s]=
t.CM_NAME +
'(' + STUFF(
(
SELECT DISTINCT UPHS.CM_NAME + ':'
FROM
PMS.UPF_PHASE UPHS,
PMS.UPF_ACTIVITY_ASSOCIATION UAA,
PMS.UPF_ACTIVITY ACT
WHERE ACT.ITEMID = UAA.ACTIVITY_ID
AND UAA.PHASE_ID = UPHS.ITEMID
AND UAA.STATUS = 'Active'
AND ACT.ITEMTYPE = 'Acty'
FOR XML PATH('')
),1,0,'') +')'
)t1
---

Also posting sample data
Plese help

Table UPHS
---
ITEMID CM_NAME
-2 Default Phase
0 Dummy Phase
43371 Study
43372 Design
43373 Coding
43374 Testing
43375 Implementation
43376 Suspect Phase
43377 Prospect Phase
43378 Closure Phase

========================

Table UAA
---
ACTIVITY_ID ACTIVITY_NAME
43724 2D Illustration
43725 3D Illustration
43726 Animation
43727 Sound editing
43728 HTML Authoring
43729 Rework on PMP
43730 Rework on SRS
43731 Rework on HLD
43732 Rework on LLD
43734 Rework on Code

====================
Table ACT
---
ITEMID CM_NAME
43587 Analysis
43588 Analysis Review
43589 Design Rework
43590 CR Analysis
43591 Code Review
43592 Code Rework
43593 Coding
43594 Coding and Integ
43595 Config mgt Execution
43596 Contract

---Thanks---
Santosh

Sachin.Nand

2937 Posts

Posted - 2010-12-03 : 07:14:53

I dont see any matching ITEMID in the 3 tables for which the comma seperated values should be generated.

PBUH

sks198117
Starting Member

46 Posts

Posted - 2010-12-03 : 07:19:35

actually i have just selected top 10 records from each tables but accidently there is no matching itemid..but i have matching records in table if i check with entire data.

Can u pls just modify the query so that i can check with enitire data.

Thanks

---Thanks---
Santosh

Sachin.Nand

2937 Posts

Posted - 2010-12-03 : 07:54:18

[code]
You are missing a order by clause.
Try this
[code]
SELECT *
FROM
(SELECT top 10 ITEMID,CM_NAME from PMS.UPF_ACTIVITY ORDER BY ITEMID) t
CROSS APPLY

(
SELECT [s]=
t.CM_NAME +
'(' + STUFF(
(
SELECT DISTINCT UPHS.CM_NAME + ':'
FROM
PMS.UPF_PHASE UPHS,
PMS.UPF_ACTIVITY_ASSOCIATION UAA,
PMS.UPF_ACTIVITY ACT
WHERE ACT.ITEMID = UAA.ACTIVITY_ID
AND UAA.PHASE_ID = UPHS.ITEMID
AND UAA.STATUS = 'Active'
AND ACT.ITEMTYPE = 'Acty'

FOR XML PATH('')
),1,0,'') +')'
)t1
[/code]

[/code]

PBUH

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-03 : 08:07:02

quote:
Originally posted by sks198117

I am stuck with my original data
i am using below query as suggested
----

SELECT *
FROM
(SELECT top 10 ITEMID,CM_NAME from PMS.UPF_ACTIVITY ) t
CROSS APPLY

(
SELECT [s]=
t.CM_NAME +
'(' + STUFF(
(
SELECT DISTINCT UPHS.CM_NAME + ':'
FROM
PMS.UPF_PHASE UPHS,
PMS.UPF_ACTIVITY_ASSOCIATION UAA,
PMS.UPF_ACTIVITY ACT
WHERE ACT.ITEMID = UAA.ACTIVITY_ID
AND UAA.PHASE_ID = UPHS.ITEMID
AND UAA.STATUS = 'Active'
AND ACT.ITEMTYPE = 'Acty'
FOR XML PATH('')
),1,0,'') +')'
)t1
---

Also posting sample data
Plese help

Table UPHS
---
ITEMID CM_NAME
-2 Default Phase
0 Dummy Phase
43371 Study
43372 Design
43373 Coding
43374 Testing
43375 Implementation
43376 Suspect Phase
43377 Prospect Phase
43378 Closure Phase

========================

Table UAA
---
ACTIVITY_ID ACTIVITY_NAME
43724 2D Illustration
43725 3D Illustration
43726 Animation
43727 Sound editing
43728 HTML Authoring
43729 Rework on PMP
43730 Rework on SRS
43731 Rework on HLD
43732 Rework on LLD
43734 Rework on Code

====================
Table ACT
---
ITEMID CM_NAME
43587 Analysis
43588 Analysis Review
43589 Design Rework
43590 CR Analysis
43591 Code Review
43592 Code Rework
43593 Coding
43594 Coding and Integ
43595 Config mgt Execution
43596 Contract

---Thanks---
Santosh

Hi,
It seems u have not used where condition inside cross apply
ACT.ItemID=t.ItemID

query like ths
SELECT *
FROM
(SELECT top 10 ITEMID,CM_NAME from PMS.UPF_ACTIVITY ) t
CROSS APPLY

(
SELECT [s]=
t.CM_NAME +
'(' + STUFF(
(
SELECT DISTINCT UPHS.CM_NAME + ':'
FROM
PMS.UPF_PHASE UPHS,
PMS.UPF_ACTIVITY_ASSOCIATION UAA,
PMS.UPF_ACTIVITY ACT
WHERE ACT.ITEMID = UAA.ACTIVITY_ID
AND UAA.PHASE_ID = UPHS.ITEMID
AND UAA.STATUS = 'Active'
AND ACT.ITEMTYPE = 'Acty'
AND ACT.ItemID=t.ItemID
FOR XML PATH('')
),1,0,'') +')'
)t1

Iam a slow walker but i never walk back

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-03 : 08:12:28

Even in Sample Data ItemID is not matching.

Vaibhav T

If I cant go back, I want to go fast...

sks198117
Starting Member

46 Posts

Posted - 2010-12-03 : 08:16:17

in this case i am getting all UPHS.CM_NAME seperated by : instead of getting only matching UPHS.CM_NAME on basis of ITEMID.

---Thanks---
Santosh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources