首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >动态TSQL大小写和未在逗号分隔列表中显示表

问动态TSQL大小写和未在逗号分隔列表中显示表
EN

Stack Overflow用户
提问于 2018-06-05 12:33:24
回答 2查看 60关注 0票数 0

嘿,我做的都是动态查询,在select语句中有一个CASE,如下所示:

代码语言:javascript
复制
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....

这很好,但是将表名放在结果中,即使它在逗号分隔的列表中没有表名。

因此,对于上面的示例,输出如下所示:

代码语言:javascript
复制
Description    |hardwarePartNumber |poc        |quantity
--------------------------------------------------------
something here |568FHT             |Bob Barker |NULL
another thing  |GT43643            |Steve Jobs |NULL
Hey hey hey    |DSTN345            |Fat Albert |NULL

注意quantity是如何为空的,因为它没有在逗号分隔的列表中找到。我想让它看起来像这样:

代码语言:javascript
复制
Description    |hardwarePartNumber |poc
-----------------------------------------------
something here |568FHT             |Bob Barker
another thing  |GT43643            |Steve Jobs
Hey hey hey    |DSTN345            |Fat Albert

因此,如果它不在逗号分隔的列表中,那么就不要在最后的查询中显示表。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2018-06-05 18:34:28

以下是答案:

代码语言:javascript
复制
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)
票数 2
EN

Stack Overflow用户

发布于 2018-06-05 12:43:23

继续等风格-使用交叉应用和STRING_SPLIT:

代码语言:javascript
复制
Select distinct
    ..
    compare BL columns against BL2.value
    ..

FROM 
    bomLine                AS BL 
    cross apply STRING_SPLIT ('Description,hardwarePartNumber,poc,', ',') BL2
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50700197

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档