February 8, 2012 at 4:27 pm
Looking for SQL (2008) query to perform a request from a table to return back for me a HTML formatted select combobox.
The following query works, and returns back the following results:
Query
DECLARE @parentID int
DECLARE @selected int
SET @parentID=0
SET @selected=2
SELECT '0' AS '@value', null as '@SELECTED', '---select one---' as 'data()'
union all
SELECT pkCatID as '@value',
case pkCatID when @selected then '1' else null end as '@SELECTED',
CatNameLU.strCatName as 'data()'
FROM CatNameLU
WHERE intCatParentID=@parentID
ORDER BY [data()] ASC
FOR XML PATH('option'), TYPE
Results:
<option value="0">---select one---</option>
<option value="1">Root 1</option>
<option value="2" SELECTED="1">Root 2</option>
<option value="3">Root 3</option>
Looking for:
I would like the final results to look something like this, but unclear on how to get the select portion around the currently working options.
<select id='somename' name='somename' onclick='dosomething();'>
<option value="0">---select one---</option>
<option value="1">Root 1</option>
<option value="2" SELECTED="1">Root 2</option>
<option value="3">Root 3</option>
</select>
I'd like to know how to have this finalized string returned, say placed within a varchar?
Any help is greatly appreciated. I have been banging my head trying to figure this out, when i should have just asked the pros for the answer.
Heads up thank you
Paul
February 8, 2012 at 7:59 pm
Hi Paul,
It would have been nicer if you could provide some sample data and the schema of your table. Anyway looking at your select query I created a sample table. Please check is it something like this that you were looking for ?
DECLARE @parentID INT
DECLARE @selected INT
SET @parentID=0
SET @selected=2
DECLARE
@id AS VARCHAR(20) = 'somename'
,@name AS VARCHAR(20) = 'somename'
,@onclick AS VARCHAR(20) = 'dosomething();'
--====== Generate Sample Data ==============
DECLARE @CatNameLU AS TABLE(
intCatParentID INT,
pkCatID INT,
strCatName VARCHAR(20)
)
INSERT INTO @CatNameLU (intCatParentID,pkCatID, strCatName)
SELECT 0,1,'Root 1' UNION
SELECT 0,2,'Root 2' UNION
SELECT 0,3,'Root 3'
--====== Logic ========
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE
Output
-------
<select id="somename" name="somename" onclick="dosomething();">
<option value="0">---select one---</option>
<option value="1">Root 1</option>
<option value="2" SELECTED="1">Root 2</option>
<option value="3">Root 3</option>
</select>
I hope this will solve your issue. 🙂
--------
Manjuke
http://www.manjuke.com
February 9, 2012 at 8:08 am
Apologies before for not supplying test data. I will definitely take this into consideration for future posts.
Tested out your code and it works 100% PERFECT! THANK YOU SO MUCH.
One quick question. When i run the query, i see the query result as a single column, but its listed as 'No column name. If I am making an ODBC call, and I want to retrieve this information as a string, how could i assign a column name to the column name, or return the value as a string?
Last question i promise 🙂
Thanks so much!
February 9, 2012 at 8:31 am
Well you could try something like this..
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
🙂
--------
Manjuke
http://www.manjuke.com
February 9, 2012 at 8:34 am
manjuke (2/9/2012)
Well you could try something like this..
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
🙂
Why have you done TOP 100 PERCENT ? It's superfluous, you'd get the same result without it: -
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
February 9, 2012 at 8:37 am
100% Spot on! Thank you so much. Take care and look forward to more assistance!
I bow before the Grasshopper, as the student still continues his lessons!
February 9, 2012 at 9:08 am
I need to ask...
Why in god sake are you returning a html control from the database?
February 9, 2012 at 9:25 am
Cadavre (2/9/2012)
manjuke (2/9/2012)
Well you could try something like this..
DECLARE @xml AS XML = (
SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT TOP 100 PERCENT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
🙂
Why have you done TOP 100 PERCENT ? It's superfluous, you'd get the same result without it: -
DECLARE @xml AS XML = (SELECT @id AS '@id',@name AS '@name',@onclick AS '@onclick',(
SELECT A.* FROM(
SELECT '0' AS '@value', NULL AS '@SELECTED', '---select one---' AS 'data()'
UNION ALL
SELECT
pkCatID AS '@value',
CASE pkCatID
WHEN @selected THEN '1' ELSE NULL END AS '@SELECTED',
strCatName AS 'data()'
FROM
@CatNameLU
WHERE
intCatParentID=@parentID
)AS A
ORDER BY A.[data()] ASC FOR XML PATH('option'), TYPE
)FOR XML PATH('select') ,TYPE)
SELECT CAST(@xml AS VARCHAR(MAX)) AS 'DesiredColumnName'
Thanks for pointing out.. I didn't notice that too.. Sorry 🙂
--------
Manjuke
http://www.manjuke.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply