How to get blank values in inner join table

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)

How to get blank values in inner join table

Author

Topic

hemant_ec48
Starting Member

7 Posts

Posted - 2009-11-30 : 07:08:13

Hi friends,

How to achieve result like below.

T1-----Q1-----A1
""-----""-----A2
""-----""-----A3
""-----Q2-----B1
""-----""-----B2
""-----""-----B3
""-----""-----B4

Here T, Q, A are connected with each other...

T=Testcode , Q=Question, A=Answer

Testcode Have Multiple Question, One question have Multiple answer

So If i select Testcode T1...Question Q1,Q2 will come...But Q1 have answer A1,A2,A3...

My problem is...

In Question column only one time Q1 should come....(in row of A1)

remaining rows Q1 should not be come....(in rows of A2 & A3)

same as for testcode...should apeear only once again all all question

Like in example...

Hemant Patel
Contact me : 09726242864

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 07:17:30

Where do you want to show the data?

Madhivanan

Failing to plan is Planning to fail

hemant_ec48
Starting Member

7 Posts

Posted - 2009-11-30 : 07:25:57

Hi Madhivanan

One ASP page is already created....In which one table is created Which read Column header & rows....&...other reports also bind to that table.....same as above one is also one report...I can't change Existing ASP page.....So I need this functionality....

Hemant Patel
Contact me : 09726242864

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 07:30:48

If you use reporting tool make use of it's Suppress if duplicated feature

Madhivanan

Failing to plan is Planning to fail

hemant_ec48
Starting Member

7 Posts

Posted - 2009-11-30 : 07:32:37

Sorry dear...here app not using any reporting tool...headache is that this is classic ASP page.....and i can't change page...

Hemant Patel
Contact me : 09726242864

Sachin.Nand

2937 Posts

Posted - 2009-11-30 : 07:41:46

Will work only if u are using SQL 2005

DECLARE @tbl AS TABLE(t VARCHAR(10),q VARCHAR(10),VALUE VARCHAR(10))
INSERT INTO @tbl
SELECT 'T1','Q1','A1' UNION ALL
SELECT 'T1','Q1','A2'UNION ALL
SELECT 'T1' ,'Q1','A3' UNION ALL
SELECT 'T1' ,'Q2','B1'UNION ALL
SELECT 'T1','Q2','B2'UNION ALL
SELECT 'T1','Q2','B3'UNION ALL
SELECT 'T1','Q2','B4'

SELECT
CASE WHEN rowt=1 THEN t ELSE '' END AS t,
CASE WHEN rowq=1 THEN q ELSE '' END AS q,
VALUE
FROM
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY t ORDER BY t)AS rowt,
ROW_NUMBER()OVER(PARTITION BY q ORDER BY q)AS rowq
FROM @tbl
)t

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 07:42:40

This is only for a fun


select 
	case when testcode_no=1 then testcode else '' end as testcode,
	case when question_no=1 then question else '' end as question,
	answer from
(
select testcode,Question,Answer,
	row_number() over (partition by testcode order by testcode) as testcode_no,
	row_number() over (partition by question order by testcode) as question_no from
(
	select 'T1' as testcode,'Q1' as question,'A1' as answer  union all
	select 'T1' as testcode,'Q1' as question,'A2' as answer  union all
	select 'T1' as testcode,'Q1' as question,'A3' as answer  union all
	select 'T1' as testcode,'Q2' as question,'B1' as answer  union all
	select 'T1' as testcode,'Q2' as question,'B2' as answer  union all
	select 'T1' as testcode,'Q2' as question,'B3' as answer  union all
	select 'T1' as testcode,'Q2' as question,'B4' as answe
) as t
) as t

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-30 : 07:50:16

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources