April 6, 2018 at 5:55 am
Hi Team,
I have below scenario.
Create table mines(
id int,
name varchar(50),
lamp_start varchar(50),
lamp_end varchar(50),
conc_start varchar(50),
conc_end varchar(50)
)
declare @category varchar(50)
Based on category values i should get records dynamically
set @category='lamp'
select id,name,lamp_start,lamp_end from mines
if set @category='conc'
select id,name,conc_start,conc_end from mines
I tried to get the values from information_schema.columns but could not integrate with able select script.
Please help me in writing the above select scripts in which column names will be dynamically selected.
Hope my question is clear.
Thanks in Advance!
April 6, 2018 at 6:04 am
Is this not just a case of...?
DECLARE @category VARCHAR(50);
SET @category='lamp';
IF @category = 'lamp' BEGIN
SELECT id,
[name],
lamp_start,
lamp_end
FROM mines;
END
ELSE IF @Category = 'conc' BEGIN
SELECT id,
[name],
conc_start,
conc_end
FROM mines;
END
Generally, however, this type of table design is frowned upon. You should consider normalising your data. For example, have a table set up of:CREATE TABLE mines(id int,
[name] varchar(50),
category varchar(50),
[start] varchar(50),
[end] varchar(50));
Or possibly (for full NF):
CREATE TABLE mines(id int,
[name] varchar(50));
CREATE TABLE category (id int,
category varchar(50));
CREATE TABLE MineCategory (id int,
mineid int,
categoryid int,
[start] varchar(50),
[end] varchar(50));
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 6, 2018 at 6:08 am
Or you can use dynamic SQL:
DECLARE @category VARCHAR(50);
DECLARE @SQL VARCHAR(1000);
--Based on category values i should get records dynamically
SET @category = 'lamp';
SET @SQL = CONCAT('select id,name,', @category, '_start,', @category, '_end from mines');
EXEC (@SQL);
But please read up on SQL injection before going down this path. Better still, listen to Thom's advice and refine your table design.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 6, 2018 at 6:59 am
Thanks for the reply.
Yes, I understand that table needs to be normalized. But is there any way that I can use the filtered column name in select query with out if else.
Because if there is more number of categories. Is there any way I can do below.
For Ex:
select column_name from information_schema.columns where table name ='mines' and column_name like @category+'%'
The output of the above scripts I am going to use in select script as mentioned above.
select id,name,column_name from mines
April 6, 2018 at 7:06 am
I suppose, if you're going to have more hcolumns than you've let on, you could do something like this:
DECLARE @category sysname = 'lamp';
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT id,' + NCHAR(10) +
N' [name],' + NCHAR(10) +
STUFF((SELECT N',' + NCHAR(10) + N' ' + QUOTENAME(c.[name])
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.[name] LIKE @category + N'%'
AND t.[name] = N'mines'
ORDER BY c.column_id ASC
FOR XML PATH(N'')),1,2,'') + NCHAR(10) +
N'FROM mines;';
PRINT @SQL;
EXEC sp_executesql @SQL;
This avoids the injection Phil was talking about. I still, however, suggest you fix your database design.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 6, 2018 at 7:06 am
rohit.kumar.barik - Friday, April 6, 2018 6:59 AMThanks for the reply.
Yes, I understand that table needs to be normalized. But is there any way that I can use the filtered column name in select query with out if else.
Because if there is more number of categories. Is there any way I can do below.
For Ex:
select column_name from information_schema.columns where table name ='mines' and column_name like @category+'%'
The output of the above scripts I am going to use in select script as mentioned above.select id,name,column_name from mines
My reply shows you how to use dynamic SQL & therefore you already have the tools to do what you need. Was any part of it unclear?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply