May 21, 2009 at 11:42 am
I am attempting to create a process the will return a variable set of data. My problem is this:
Table definition:
create table test (
name varchar(100),
startDate datetime,
data1 bit,
data2 bit,
data3 bit,
data4 bit,
data5)
I would like the result set to be variable based on what data bits are 1. Such as, (mike, 05/21/09, data1, data3, data5) if data1, data3, and data5 where marked 1. The best part is that this has to occur in t-sql. I only have read access to this database. I am sorry if this is too vague but I will answer any questions.
May 21, 2009 at 11:49 am
So let's start with the "any questions" :hehe:
1.)
What do you mean with "create a process"? A procedure? A .Net app? A web-app? PHP? Cobol?
2.)
I don't really understand your data1-data5 bits. Do you mean you want to specify the columns which have to be returned by the process
3.)
Does your table contain the BITs or do the BITs specify the columns of the table name stored in column "name"?
May 21, 2009 at 11:56 am
dynamic results means dynamic SQL. there's no other way to do it.
if you only have read access, you could not create a stored procedure to run dynamic sql anyway, right?
basically, you'll need to build a query on the client side then,so you do something like"
create myproc(
dim @sql varchar(8000)
SET @sql = 'SELECT name,StartDate'
if @data1 = 1
SET @sql = @sql + ',data1
if @data2 = 1
SET @sql = @sql + ',data2
if @data3 = 1
SET @sql = @sql + ',data3
if @data4 = 1
SET @sql = @sql + ',data4
if @data5 = 1
SET @sql = @sql + ',data5
SET @sql = " FROM MYTABLE WHERE name='" + @name + ''' AND StartDate = ''' + @startDate + ''''
exec(sql)
Lowell
May 21, 2009 at 12:01 pm
Florian Reischl (5/21/2009)
So let's start with the "any questions" :hehe:1.)
What do you mean with "create a process"? A procedure? A .Net app? A web-app? PHP? Cobol?
2.)
I don't really understand your data1-data5 bits. Do you mean you want to specify the columns which have to be returned by the process
3.)
Does your table contain the BITs or do the BITs specify the columns of the table name stored in column "name"?
1) Process meant anything that would work I guess. I need it to be in t-sql but I only have read access to the database
2) Yes, if data1=1 then I need to see that column in the result set but if data1=0 then I don't want that column in the result set
3) BIT is the datatype of the columns data1-5. The records will have a 0 or 1 in these columns.
Thanks
May 21, 2009 at 12:02 pm
Lowell (5/21/2009)
dynamic results means dynamic SQL. there's no other way to do it.if you only have read access, you could not create a stored procedure to run dynamic sql anyway, right?
basically, you'll need to build a query on the client side then,so you do something like"
create myproc(
dim @sql varchar(8000)
SET @sql = 'SELECT name,StartDate'
if @data1 = 1
SET @sql = @sql + ',data1
if @data2 = 1
SET @sql = @sql + ',data2
if @data3 = 1
SET @sql = @sql + ',data3
if @data4 = 1
SET @sql = @sql + ',data4
if @data5 = 1
SET @sql = @sql + ',data5
SET @sql = " FROM MYTABLE WHERE name='" + @name + ''' AND StartDate = ''' + @startDate + ''''
exec(sql)
This is what I was thinking also. I was going to try to do something with temp tables but I am not sure how that would some close either.
Thanks
May 21, 2009 at 12:13 pm
Hi Bruce
I'm still quiet unsure if I got you but let's give it a try (works only with temp tables)...
As I understood the returned columns shall depend on the resulting data. Try this:
CREATE TABLE #TestBits (Id INT NOT NULL IDENTITY, Data1 BIT, Data2 BIT, Data3 BIT, Data4 BIT)
INSERT INTO #TestBits
SELECT 1, 0, 1, 0
UNION ALL SELECT 0, 1, 1, 0
UNION ALL SELECT 0, 0, 1, 1
GO
---==========================
-- Some query criterion
DECLARE @Id INT
SELECT @Id = 2
---==========================
-- Get data for the specified criterion
SELECT *
INTO #Result
FROM #TestBits
WHERE Id = @Id
---==========================
-- Build the dynamic query
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'SELECT Id '
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data1 = 1)
SELECT @sql = @sql + ', Data1'
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data2 = 1)
SELECT @sql = @sql + ', Data2'
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data3 = 1)
SELECT @sql = @sql + ', Data3'
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data4 = 1)
SELECT @sql = @sql + ', Data4'
SELECT @sql = @sql + ' FROM #Result'
---============================
-- Return the data and drop the temp table(s)
EXECUTE (@sql)
DROP TABLE #Result
GO
DROP TABLE #TestBits
Greets
Flo
May 21, 2009 at 12:27 pm
Florian Reischl (5/21/2009)
Hi BruceI'm still quiet unsure if I got you but let's give it a try (works only with temp tables)...
As I understood the returned columns shall depend on the resulting data. Try this:
CREATE TABLE #TestBits (Id INT NOT NULL IDENTITY, Data1 BIT, Data2 BIT, Data3 BIT, Data4 BIT)
INSERT INTO #TestBits
SELECT 1, 0, 1, 0
UNION ALL SELECT 0, 1, 1, 0
UNION ALL SELECT 0, 0, 1, 1
GO
---==========================
-- Some query criterion
DECLARE @Id INT
SELECT @Id = 2
---==========================
-- Get data for the specified criterion
SELECT *
INTO #Result
FROM #TestBits
WHERE Id = @Id
---==========================
-- Build the dynamic query
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'SELECT Id '
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data1 = 1)
SELECT @sql = @sql + ', Data1'
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data2 = 1)
SELECT @sql = @sql + ', Data2'
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data3 = 1)
SELECT @sql = @sql + ', Data3'
IF EXISTS (SELECT TOP(1) 1 FROM #Result WHERE Data4 = 1)
SELECT @sql = @sql + ', Data4'
SELECT @sql = @sql + ' FROM #Result'
---============================
-- Return the data and drop the temp table(s)
EXECUTE (@sql)
DROP TABLE #Result
GO
DROP TABLE #TestBits
Greets
Flo
Sorry, I am having a hard time explaining this but I see where you are going with the code. My problem is that this changes per row. Row 1 might only have data1 and data3 set to 1 but row 2 might have data2 and data4 set to 1.
Here is some sample data that might help.
mike, 05/21/09, 1, 0, 0, 1, 0
bruce, 05/21/08, 0, 1, 1, 0, 0
john, 09/09/09, 0, 0, 0, 0, 1
I guess there is another layer here also and I didn't realize how big a part this would play. When a field is set to 1 I need to display a certain string in the results. If data1 = 1 then display 'blue' but if data1 = 1 and data2 = 1 then display 'blue', 'green'.
May 21, 2009 at 12:52 pm
Bruce (5/21/2009)
Here is some sample data that might help.mike, 05/21/09, 1, 0, 0, 1, 0
bruce, 05/21/08, 0, 1, 1, 0, 0
john, 09/09/09, 0, 0, 0, 0, 1
I guess there is another layer here also and I didn't realize how big a part this would play. When a field is set to 1 I need to display a certain string in the results. If data1 = 1 then display 'blue' but if data1 = 1 and data2 = 1 then display 'blue', 'green'.
I think we come closer 😉
Do you mean you want to return the data as one column in your result? If not could you please provide the expected result and columns for your provided sample data?
May 21, 2009 at 1:03 pm
Florian Reischl (5/21/2009)
Bruce (5/21/2009)
Here is some sample data that might help.mike, 05/21/09, 1, 0, 0, 1, 0
bruce, 05/21/08, 0, 1, 1, 0, 0
john, 09/09/09, 0, 0, 0, 0, 1
I guess there is another layer here also and I didn't realize how big a part this would play. When a field is set to 1 I need to display a certain string in the results. If data1 = 1 then display 'blue' but if data1 = 1 and data2 = 1 then display 'blue', 'green'.
I think we come closer 😉
Do you mean you want to return the data as one column in your result? If not could you please provide the expected result and columns for your provided sample data?
The columns are the same order of the table.
Name, startDate, data1, data2, data3, data4, data5
As for the results, the name and statDate columns need to be in their own field but the rest can be combined into 1 column with comma separated valuese. So the final output would be
so it would be something like
select name, startDate, aggregateFunction(data1,data2,data3,data4,data5) as output from test
Name| startDate| output
mike| 05/21/09| blue,red
bruce| 05/21/08| green, yellow
john| 09/09/09| violet
May 21, 2009 at 1:14 pm
Try this:
DECLARE @T TABLE (Name VARCHAR(100), StartDate DATETIME, Data1 BIT, Data2 BIT, Data3 BIT, Data4 BIT, Data5 BIT)
INSERT INTO @T
SELECT 'mike', '05/21/09', '1', '0', '0', '1', '0'
UNION ALL SELECT 'bruce', '05/21/08', '0', '1', '1', '0', '0'
UNION ALL SELECT 'john', '09/09/09', '0', '0', '0', '0', '1'
DECLARE @Results TABLE (Name VARCHAR(100), StartDate DATETIME, Colors VARCHAR(100))
INSERT INTO @Results
SELECT
Name,
StartDate,
CASE WHEN Data1 = 1 THEN 'blue,' ELSE '' END +
CASE WHEN Data2 = 1 THEN 'green,' ELSE '' END +
CASE WHEN Data3 = 1 THEN 'yellow,' ELSE '' END +
CASE WHEN Data4 = 1 THEN 'black,' ELSE '' END +
CASE WHEN Data5 = 1 THEN 'violet,' ELSE '' END
FROM @T
---======================
-- If the trailing "," is a problem
UPDATE @Results SET Colors = LEFT(Colors, LEN(Colors) - 1)
SELECT * FROM @Results
If the trailing comma is no problem you can remove the @Results table and direct return the data.
May 21, 2009 at 1:19 pm
Looks like this will do exactly what I need.
Thank you very much
May 21, 2009 at 1:23 pm
Glad that we could help!
May 21, 2009 at 7:31 pm
Florian Reischl (5/21/2009)
If the trailing comma is no problem you can remove the @Results table and direct return the data.
Or, use a leading comma and STUFF it just like you would if it were SQL Server 2005 and you were using XML.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2009 at 7:51 pm
Like this...
[font="Courier New"]--===== Create a test table. This is NOT part of the solution
DECLARE @T TABLE
(
Name VARCHAR(100),
StartDate DATETIME,
Data1 BIT,
Data2 BIT,
Data3 BIT,
Data4 BIT,
Data5 BIT
)
--===== Populate the test table with data.
-- Again, this is not part of the solution.
INSERT INTO @T
SELECT 'Mike' , '05/21/09', '1', '0', '0', '1', '0' UNION ALL
SELECT 'Bruce', '05/21/08', '0', '1', '1', '0', '0' UNION ALL
SELECT 'John' , '09/09/09', '0', '0', '0', '0', '1'
--===== Use STUFF to kill the leading comma on the concatenation
SELECT Name,
StartDate,
STUFF(CASE Data1 WHEN 1 THEN ',Blue' ELSE '' END
+ CASE Data2 WHEN 1 THEN ',Green' ELSE '' END
+ CASE Data3 WHEN 1 THEN ',Yellow' ELSE '' END
+ CASE Data4 WHEN 1 THEN ',Black' ELSE '' END
+ CASE Data5 WHEN 1 THEN ',Violet' ELSE '' END
,1,1,'') AS Colors
FROM @T
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2009 at 1:49 am
Jeff Moden (5/21/2009)
Florian Reischl (5/21/2009)
If the trailing comma is no problem you can remove the @Results table and direct return the data.Or, use a leading comma and STUFF it just like you would if it were SQL Server 2005 and you were using XML.
I didn't see the wood for the trees just to use a leading comma instead of a trailing...
Thanks Jeff!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply