Buils a select statement from another select statement

Question

Post reply

Buils a select statement from another select statement

cmaciasg

Old Hand

Points: 321
More actions
March 19, 2009 at 11:21 am

#127177

HI all, i've a problem and I dont find any solution
I need to build a select statement with a especific columns from another select statement and I dont know how do it.
I've got a table with this record:
id,name, col1, col2, col3
1, Carlos, 1, 0, 1
If the value of col1, col2 o col3 is 1 I want build the next select:
SELECT col1, col3 FROM Table.
If the record is
id,name, col1, col2, col3
1, Carlos, 1, 1, 1
The select is:
SELECT col1, col2, col3 FROM Table
I hope yoy can help me, Im very frustrated.
Thank you all.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Try this:

DECLARE @table TABLE

(id INT,

Name VARCHAR(10),

Col1 BIT,

Col2 BIT,

Col3 BIT)

INSERT INTO @table

SELECT 1, 'Carlos', 1, 0, 1

--START

DECLARE @SQL VARCHAR(MAX)

SELECT [CoLName], [Include]

FROM

(SELECT Col1, Col2, Col3

FROM @table) p

UNPIVOT

([Include] FOR ColName IN

(Col1, Col2, Col3)

)AS unpvt

SELECT @SQL = ISNULL(@SQL,'') + CASE WHEN [Include] = 1 THEN ',' + [ColName]+ ' ' ELSE '' END

FROM

(SELECT [CoLName], [Include]

FROM

(SELECT Col1, Col2, Col3

FROM @table) p

UNPIVOT

([Include] FOR ColName IN

(Col1, Col2, Col3)

)AS unpvt) n

SET @SQL = 'SELECT ' + STUFF(@SQL,1,1,'') + 'FROM [TABLENAME]'

SELECT @SQL

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

cmaciasg Old Hand Points: 321 More actions · Answer 2

Perfect!!!! 😀

Thank you very much, you have saved my life, 🙂

cmaciasg Old Hand Points: 321 More actions · Answer 3

Only a more questions, for maintenance issues.

How could manage all that stuff dinamically? That is, If the columns aren´t 3, but many of them, col1, col2, col3, ...., coln. Is there some way???

Thank you again, Christopher

cmaciasg Old Hand Points: 321 More actions · Answer 4

cmaciasg

Old Hand

Points: 321

March 20, 2009 at 3:20 am

#963375

Error.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

are you saying that when you run your proc you have no idea what the columns are?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

cmaciasg (3/19/2009)
Only a more questions, for maintenance issues.
How could manage all that stuff dinamically? That is, If the columns aren´t 3, but many of them, col1, col2, col3, ...., coln. Is there some way???
Thank you again, Christopher

Similar to what's in the following article... with dynamic SQL...

http://www.sqlservercentral.com/articles/cross+tab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)