Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

column selection on dynamic basis Expand / Collapse
Author
Message
Posted Sunday, February 24, 2013 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 4:39 AM
Points: 4, Visits: 6
Hi,

How can i select columns of a table dynamically through a select statement if the column name need to be changed based on the value selected in a Combo Box in a different form.

it's like select column A from table X if combo box selection is A, select column B from same table if combo box selection is B etc..

Please assist

Regards,
asela115

Post #1423434
Posted Sunday, February 24, 2013 12:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 7,161, Visits: 13,234
Assuming the same WHERE clause is used, you could use the CASE statement in your SELECT statement.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1423444
Posted Sunday, February 24, 2013 6:21 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
asela115 (2/24/2013)
Hi,

How can i select columns of a table dynamically through a select statement if the column name need to be changed based on the value selected in a Combo Box in a different form.

it's like select column A from table X if combo box selection is A, select column B from same table if combo box selection is B etc..

Please assist

Regards,
asela115


I would use dynamic sql like this:

First some sample data


IF OBJECT_ID('#TestTable') IS NOT NULL
DROP TABLE #TestTable

CREATE TABLE #TestTable (
ID INT IDENTITY(1,1) NOT NULL,
EmplID INT NULL,
MgrID INT NULL,
CustID INT NULL,
LocID INT NULL,
PRIMARY KEY (ID))

INSERT INTO #TestTable
SELECT 111,1011,10001,789 UNION
SELECT 222,1022,10002,890 UNION
SELECT 333,1033,10003,789 UNION
SELECT 444,1044,10004,890 UNION
SELECT 555,1055,10005,789 UNION
SELECT 666,1066,10006,890 UNION
SELECT 777,1077,10007,789 UNION
SELECT 888,1088,10008,890 UNION
SELECT 999,1099,10009,678

SELECT * FROM #TestTable


The code:


DECLARE
@Response1 VARCHAR(50)
,@Response2 VARCHAR(50)
,@LocID INT
,@strSQL VARCHAR(MAX)

SET @Response1 = 'EmplID'
SET @Response2 = 'CustID'
SET @LocID = 890

SET @strSQL = '
SELECT
t.ID
,'+@Response1+' AS '+@Response1+'
,'+@Response2+' AS '+@Response2+'
FROM
#TestTable AS t
WHERE
LocID = '+CAST(@LocID AS VARCHAR(5))+'
'

EXEC(@strSQL)


The output:


ID EmplID CustID
2 222 10002
4 444 10004
6 666 10006
8 888 10008


Post #1423454
Posted Sunday, February 24, 2013 7:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 4:39 AM
Points: 4, Visits: 6
thanks you all for your prompt response. I'm so grateful for you all for spending your valuable time on assisting people like us.

let me clear my requirement as follows. I'm not going to do a sophisticated thing as it appears. sorry if my original post give an incorrect impression about my requirement

see, I have a access table with column headings like below(say table1)

year , month , feeder name , FaultType1 , FaultType2 , FaultType3

now I have created a form with combo boxes to select year month and FaultType etc... and i need to draw a pivot chart based on my selection in access form.

it's easy to get the combo box values for year, month(for instance combo value for Year will be 2011,2012 etc..). code like below meet that requirement
select Year from Table1 where Table1.Year=[Forms]![FormName]![Combo Name]etc..

however I need user to select FaultType through another combo box(in the same form) and then draw a pivot chart based on the FaultType he selected. my combo box drop down values should be FaultType1, Faultype2,FaultType3 which is infact the column names in my Table1 and once he select a specific FaultType then a pivot chart should be drawn based on that selection.

to do that I guess I have to write a SQL statement first to create another dynamic table based on user's selection and then draw the pivot chart

my SQL query will be like below one;

SELECT Year, Month, FeederName , "and the Fault type based on the user's selection through combo box" FROM Table1 WHERE etc...

Here the FaultType is actually the column names in my Table1

I need a help to fill the " " part in my above SQL which is on how to select the FaultType(1 or 2 or 3) from Table1 based on the users selection

Thanks for helping

Post #1423457
Posted Monday, February 25, 2013 7:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Just use the same code I posted above except change it around to suit your needs.


IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
DROP TABLE #TestTable

CREATE TABLE #TestTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NULL,
[Month] INT NULL,
[FaultType1] VARCHAR(50) NULL,
[FaultType2] VARCHAR(50) NULL,
[FaultType3] VARCHAR(50) NULL,
PRIMARY KEY (ID))

INSERT INTO #TestTable
SELECT 2011,11,'Mfg Defect','','' UNION ALL
SELECT 2012,1,'','Power Overload','' UNION ALL
SELECT 2012,3,'','','User Error' UNION ALL
SELECT 2012,4,'Mfg Defect','','' UNION ALL
SELECT 2012,4,'','','User Error' UNION ALL
SELECT 2012,6,'','','User Error' UNION ALL
SELECT 2013,1,'','Power Overload','' UNION ALL
SELECT 2013,1,'Mfg Defect','','' UNION ALL
SELECT 2013,2,'','Power Overload',''

--SELECT * FROM #TestTable


DECLARE
@Response VARCHAR(50)
,@Year INT
,@strSQL VARCHAR(MAX)

SET @Response = 'FaultType1'
SET @Year = 2012

--SET @Response = 'FaultType2'
--SET @Year = 2011

--SET @Response = 'FaultType3'
--SET @Year = 2012

SET @strSQL = '
SELECT
t.[ID]
,t.[Year]
,t.[Month]
,'+@Response+' AS '+@Response+'
FROM
#TestTable AS t
WHERE
NULLIF('+@Response+','''') IS NOT NULL
AND Year >= '+CAST(@Year AS VARCHAR(5))+'
ORDER BY
Year, MONTH
'

EXEC(@strSQL)


The point to take away from this is that to pass in metadata such as table or column names requires some dynamic sql somewhere. If this is going to occur a lot it MIGHT be beneficial to put the table and column mappings into a separate table then join on that. If you know the column names already and they are limited in number I think the dynamic sql approach is as good as any.

Post #1423842
Posted Monday, February 25, 2013 9:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
OK, I figured out another way to do this where you can pass in the column by variable. This method is called a Partitioned View. It requires that you set up a separate table for each fault status. So in your case where you now have three columns in one table you would have three tables with a single 'fault' column instead.

THEN...you create a Partitioned View that combines the three tables. If you set up the tables and related view correctly you will be able to pass in a variable to get the results from the proper column.

OK, first set up the tables and populate with test data. NOTE: The CHECK constraints are the key to this whole process.


IF OBJECT_ID('dbo.FaultTable1') IS NOT NULL
DROP TABLE dbo.FaultTable1
IF OBJECT_ID('dbo.FaultTable2') IS NOT NULL
DROP TABLE dbo.FaultTable2
IF OBJECT_ID('dbo.FaultTable3') IS NOT NULL
DROP TABLE dbo.FaultTable3

CREATE TABLE dbo.FaultTable1 (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NOT NULL,
[Month] INT NULL,
[FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'Mfg Defect'),
PRIMARY KEY ([ID],[Year]))

CREATE TABLE dbo.FaultTable2 (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NOT NULL,
[Month] INT NULL,
[FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'Power Overload'),
PRIMARY KEY ([ID],[Year]))

CREATE TABLE dbo.FaultTable3 (
[ID] INT IDENTITY(1,1) NOT NULL,
[Year] INT NOT NULL,
[Month] INT NULL,
[FaultType] VARCHAR(50) NULL CHECK ([FaultType] = 'User Error'),
PRIMARY KEY ([ID],[Year]))


INSERT INTO FaultTable1
SELECT 2011,11,'Mfg Defect' UNION ALL
SELECT 2012,4,'Mfg Defect' UNION ALL
SELECT 2013,1,'Mfg Defect'

INSERT INTO FaultTable2
SELECT 2012,1,'Power Overload' UNION ALL
SELECT 2013,1,'Power Overload' UNION ALL
SELECT 2013,2,'Power Overload'

INSERT INTO FaultTable3
SELECT 2012,3,'User Error' UNION ALL
SELECT 2012,4,'User Error' UNION ALL
SELECT 2012,6,'User Error'


Now create the schema-bound view:


CREATE VIEW dbo.vwFaults
WITH SCHEMABINDING
AS
SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM dbo.FaultTable1
UNION ALL
SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM dbo.FaultTable2
UNION ALL
SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM dbo.FaultTable3


Finally, you can query this view and pass in the FaultType as a variable. Look at the query plan and you will see that it does the union of the three tables with a clustered index seek.


DECLARE @FaultType VARCHAR(50)
SET @FaultType = 'Power Overload'

SELECT
[ID],
[Year],
[Month],
[FaultType]
FROM
dbo.vwFaults
WHERE
FaultType = @FaultType
AND ID > 0


Sample output:


ID Year Month FaultType
1 2012 1 Power Overload
2 2013 1 Power Overload
3 2013 2 Power Overload


 
Post #1423869
Posted Tuesday, February 26, 2013 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 7,161, Visits: 13,234
Based on Stevens easy to use sample data (STRONG hint for future questions... ) here's my approach. The main difference is the "depending column" having a static header ("FaultType", similar to Stevens approach using three tables).

The reasons why I wouldn't use one of Stevens approaches:
a) dynamic SQL: I'm trying to avoid it where it's not absolutely required since it makes the code harder to read (especially if there are multiple quotes involved) and there's a risk of SQL injection that needs to be taken care of. @Steven: I'd always use sp_executesql in such a scenario instead of exec().
b) separaet tables combined in a union-based view: this concept violates normalization and would require additional table(s) if there are new fault types. I'd strongly vote against such a concept.

;
WITH cte as
(
SELECT
t.[ID]
,t.[Year]
,t.[Month]
, CASE WHEN @Response = 'FaultType1' THEN FaultType1
WHEN @Response = 'FaultType2' THEN FaultType2
WHEN @Response = 'FaultType3' THEN FaultType3
END AS FaultType
FROM
#TestTable AS t
WHERE Year >= @Year
)
SELECT * FROM cte
WHERE FaultType>''
ORDER BY Year, MONTH





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1424055
Posted Thursday, February 28, 2013 12:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 4:39 AM
Points: 4, Visits: 6
Hi,

thank you all for the responses given on this

however I came to know that ACCESS SQL doesn't support case when statements. is that correct?

further, during past few days I was looking for many options and found that IIF function too can be used to handle this sort of situation.

for instance;

select Year, Month,iif(ComboboxA = 'FT1',FaultType1,iif(ComboboxA = 'FT2',FaultType2,FaultType3)) as FaultType from Table1.

however each time when i make selection in my ComboboxA Combo Box and refresh the related table it asks to enter parameter value this Combo Box.
(I have attached the error message with this)

Any idea on why it asks to enter such parameter?
I f I can overcome this issue I guess my work is done

anyway thanks for the help on this :)


  Post Attachments 
error.jpg (0 views, 252.24 KB)
Post #1424925
Posted Thursday, February 28, 2013 10:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 7,161, Visits: 13,234
Now that is a slightly different story...
You asked a question in a SQL Server forum without even mentioning you're talking about MS Access.
You might find someone around answering your question. But that would be accidentially. You might want to ask in a MS Access related forum.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1425177
Posted Wednesday, March 6, 2013 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 4:39 AM
Points: 4, Visits: 6
Hi all,

Finally i was able to fix this

Sorry for posting this in a incorrect thread.

However I was able to solve it through using IIF function in access

Thanks again
Asela
Post #1427306
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse