嘿,我做的都是动态查询,在select语句中有一个CASE,如下所示:
DECLARE @valueList varchar(8000)
DECLARE @pos int
DECLARE @len int
DECLARE @value varchar(8000)
SET @valueList = 'Description,hardwarePartNumber,poc,'
BEGIN
SET @len = CHARINDEX(',', @valueList, @pos+1) - @pos
SET @value = SUBSTRING(@valueList, @pos, @len)
SELECT
CAST(CASE WHEN @value = 'Description' THEN
BL.Description END AS varchar) AS 'Description',
CAST(CASE WHEN @value = 'hardwarePartNumber' THEN
BL.hardwarePartNumber END AS varchar) AS 'hardwarePartNumber',
CAST(CASE WHEN @value = 'poc' THEN
UT.lastFirstMiddle END AS int) AS 'poc',
CAST(CASE WHEN @value = 'quantity' THEN
BL.quantity END AS int) AS 'quantity',
etc...etc....
FROM
bLine AS BL
INNER JOIN usersTbl AS UT
ON BL.poc = UT.id
etc...etc....这很好,但是将表名放在结果中,即使它在逗号分隔的列表中没有表名。
因此,对于上面的示例,输出如下所示:
Description |hardwarePartNumber |poc |quantity
--------------------------------------------------------
something here |568FHT |Bob Barker |NULL
another thing |GT43643 |Steve Jobs |NULL
Hey hey hey |DSTN345 |Fat Albert |NULL注意quantity是如何为空的,因为它没有在逗号分隔的列表中找到。我想让它看起来像这样:
Description |hardwarePartNumber |poc
-----------------------------------------------
something here |568FHT |Bob Barker
another thing |GT43643 |Steve Jobs
Hey hey hey |DSTN345 |Fat Albert因此,如果它不在逗号分隔的列表中,那么就不要在最后的查询中显示表。
发布于 2018-06-05 18:34:28
以下是答案:
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
SELECT CAST(BL.Description AS varchar) AS 'Description',
CAST( BL.hardwarePartNumber AS varchar) AS 'hardwarePartNumber',
CAST(UT.lastFirstMiddle END AS int) AS 'poc',
CAST(BL.quantity END AS int) AS 'quantity',
etc...etc....
INTO #Temp
FROM
bLine AS BL
INNER JOIN usersTbl AS UT
ON BL.poc = UT.id
etc...etc....
DECLARE @valueList varchar(8000)
SET @valueList = 'Description,hardwarePartNumber,poc,'
DECLARE @SQL varchar(max) = 'SELECT ' + LEFT(@valuelist,LEN(@valuelist)-1 + ' FROM #Temp'
EXEC(@SQL)发布于 2018-06-05 12:43:23
继续等风格-使用交叉应用和STRING_SPLIT:
Select distinct
..
compare BL columns against BL2.value
..
FROM
bomLine AS BL
cross apply STRING_SPLIT ('Description,hardwarePartNumber,poc,', ',') BL2https://stackoverflow.com/questions/50700197
复制相似问题