Select query with Parent ID columns

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

Transact-SQL (2008)

Select query with Parent ID columns

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-01-05 : 15:29:20

Hi,
I got a table with some questionnarie columns and some questions with sub_questions. For sub question rows Parent Question ID in assigned to parent_ID

Ex: TableA:

[sid] [gid] [qid] [ParentId] [SQID][questions]
AA 100 1 0 - Q1
AA 100 2 0 - Q2
AA 100 3 2 a q1
AA 100 4 2 b q2
AA 100 5 0 - Q3

From the above table I need the results with joining all the columns and questions.
Where as If qid comes in ParentId then qid row will not appear, but its question will concatenate with parentId row question and ID forms with ParentId and SQID

Expecting Result:
[ID] [Question]
AA-100-1 Q1
AA-100-2a Q2-q1
AA-100-2b Q2-q2
AA-100-5 Q3

Please help me in this to get the solution..

Regards,
Kalaiselvan R
Love Yourself First....

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-05 : 17:37:26

[CODE]declare @tbl table (
sid char(2),
gid varchar(10),
qid varchar(10),
ParentID varchar(10),
sqid char(1),
questions varchar(100)
)

insert into @tbl
values
('AA', 100, 1, 0, '-', 'Q1'),
('AA', 100, 2, 0, '-', 'Q2'),
('AA', 100, 3, 2, 'a', 'q1'),
('AA', 100, 4, 2, 'b', 'q2'),
('AA', 100, 5, 0, '-', 'Q3')

select
a.sid,
a.gid,
coalesce(b.ParentID + b.sqid, a.qid),
a.questions + coalesce('-' + b.questions, '')
from
@tbl a
left outer join
@tbl b
on a.sid = b.sid -- I am assuming that you need this
and a.gid = b.gid -- I am assuming that you need this
and a.qid = b.ParentID -- You Need This!
where
a.sqid = '-'[/CODE]

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources