代码之家 › 专栏 › 技术社区 › StealthRT

动态tsql大小写,不显示不在逗号分隔列表中的表

sql-server-2014 tsql sql-server sql

StealthRT · 技术社区 · 7 年前

嘿我只是想动态询问并拥有案例在我的select语句中,如下所示:

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

注意如何量是 无效的 因为在 逗号分隔 名单。我想让它看起来是这样的:

Description    |hardwarePartNumber |poc
-----------------------------------------------
something here |568FHT             |Bob Barker
another thing  |GT43643            |Steve Jobs
Hey hey hey    |DSTN345            |Fat Albert

所以,如果它不在 逗号分隔 列表则不在最终查询中显示该表。

2 回复 | 直到 7 年前

StealthRT 7 年前

答案如下:

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)

cloudsafe 7 年前

继续ETC样式-使用交叉应用和字符串分割:

Select distinct
    ..
    compare BL columns against BL2.value
    ..

FROM 
    bomLine                AS BL 
    cross apply STRING_SPLIT ('Description,hardwarePartNumber,poc,', ',') BL2