Union all fields of tables with conditions

Question

Post reply

Union all fields of tables with conditions

nguyennd

SSCommitted

Points: 1881
More actions
September 6, 2010 at 1:17 am

#229060

I have 3 tables A(A1,A2), B(B1,B2), C(C1,C2)
Create table A(A1 int,A2 int)
Create table B(B1 int,B2 int)
Create table C(C1 int,C2 int)
INSERT INTO A VALUES(1,1)
INSERT INTO B VALUES(2,2)
INSERT INTO C VALUES(3,3)
and I have 3 varibles @CheckA,@CheckB,@CheckC
Declare @CheckA bit
Declare @CheckB bit
Declare @CheckC bit
Now, i want to write SQL such as:
If(@CheckA = 1) Select * from A
combine
If(@CheckB = 1) Select * from B
combine
If(@CheckC = 1) Select * from C
Results:
If @CheckA = 1,@CheckB = 0,@CheckC=1 then result as below:
A1 A2 C1 C2
1 1 3 3
If @CheckA = 1,@CheckB = 1,@CheckC=1 then result as below:
A1 A2 B1 B2 C1 C2
1 1 2 2 3 3
thank you so much for any help/insight/direction you can offer.
http://unitech.vn

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Will tables A, B and C have only one row per table all the time?

nguyennd SSCommitted Points: 1881 More actions · Answer 2

ColdCoffee (9/6/2010)
Will tables A, B and C have only one row per table all the time?

No, may be many have row all the time

ex:

A(1,1),B(2,2),B(4,4),C(3,3)

@CheckA=1,@CheckB=1,@CheckC=1

Result:

A1 A2 B1 B2 C1 C2

1 1 2 2 3 3

4 4

http://unitech.vn

LutzM SSC Guru Points: 107049 More actions · Answer 3

I think you're looking for UNION ALL...

Select A1, A2 from A WHERE @CheckA = 1

UNION ALL

Select B1, B2 from B WHERE @CheckB = 1

UNION ALL

Select C1, C2 from C WHERE @CheckC = 1

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

How is this ?

IF OBJECT_ID('A') IS NOT NULL

DROP TABLE A

IF OBJECT_ID('B') IS NOT NULL

DROP TABLE B

IF OBJECT_ID('C') IS NOT NULL

DROP TABLE C

Create table A( ID INT IDENTITY(1,1) , A1 int,A2 int)

Create table B( ID INT IDENTITY(1,1) , B1 int,B2 int)

Create table C( ID INT IDENTITY(1,1) , C1 int,C2 int)

INSERT INTO A VALUES(1,1)

INSERT INTO B VALUES(2,2)

INSERT INTO C VALUES(3,3)

IF OBJECT_ID('tempdb..#TAB ') IS NOT NULL

DROP TABLE #TAB

CREATE TABLE #TAB

(

Table_Name VARCHAR(1),

Col_Names VARCHAR(2),

Value INTEGER

)

INSERT INTO #TAB

SELECT 'A' , Col_Names , Value FROM

(SELECT * FROM A) UNPIVOT_TABLE

UNPIVOT

(Value FOR Col_Names IN ([A1] , [A2]) ) UNPIVOT_HANDLE

UNION ALL

SELECT 'B' , Col_Names , Value FROM

(SELECT * FROM B) UNPIVOT_TABLE

UNPIVOT

(Value FOR Col_Names IN ([B1] , [B2]) ) UNPIVOT_HANDLE

UNION ALL

SELECT 'C' , Col_Names , Value FROM

(SELECT * FROM C) UNPIVOT_TABLE

UNPIVOT

(Value FOR Col_Names IN ([C1] , [C2]) ) UNPIVOT_HANDLE

SELECT * FROM #TAB

Declare @CheckA bit

Declare @CheckB bit

Declare @CheckC bit

SELECT @CheckA = 1,@CheckB = 1,@CheckC=1

DECLARE @ACols VARCHAR(15)

DECLARE @BCols VARCHAR(15)

DECLARE @CCols VARCHAR(15)

DECLARE @SELECT VARCHAR(1024)

DECLARE @PivotOps VARCHAR(1024)

SET @ACols = ', [A1] , [A2]'

SET @BCols = ', [B1] , [B2]'

SET @CCols = ', [C1] , [C2]'

SELECT @SELECT = CASE WHEN @CheckA = 1 THEN @ACols ELSE '' END +

CASE WHEN @CheckB = 1 THEN @BCols ELSE '' END +

CASE WHEN @CheckC = 1 THEN @CCols ELSE '' END

SELECT @PivotOps =

'SELECT '+ STUFF ( @SELECT , 1, 1, '') + ' FROM

(SELECT Col_Names, Value FROM #TAB ) PIVOT_TABLE

PIVOT

(MAX(Value) FOR Col_Names IN ('+ STUFF ( @SELECT , 1, 1, '') +') ) PIVOT_HANDLE'

PRINT @PivotOps

EXEC (@PivotOps)