SQL Server 2008 中生成动态查询的字符串函数

发布于 2024-12-15 13:22:13 字数 566 浏览 2 评论 0原文

Select * from MyTable 给出以下结果

AttributeID                          AttributeName
------------------------------------ ------------------------
6B93119B-263B-4FED-AA89-198D26A3A3C4 DOB
E27DBA94-F387-460A-BC02-84878692BDF6 Sex
ABF3B85C-0DEA-44FE-857A-AC63520F7294 History

现在我想生成以下格式的动态查询（与 PIVOT 一起使用）

 SELECT 
  [6B93119B-263B-4FED-AA89-198D26A3A3C4] DOB,

  [E27DBA94-F387-460A-BC02-84878692BDF6] Sex,

  [ABF3B85C-0DEA-44FE-857A-AC63520F7294] History

如何在 SQL Server 2008 中执行此操作？

原文

Select * from MyTable gives the following result

AttributeID                          AttributeName
------------------------------------ ------------------------
6B93119B-263B-4FED-AA89-198D26A3A3C4 DOB
E27DBA94-F387-460A-BC02-84878692BDF6 Sex
ABF3B85C-0DEA-44FE-857A-AC63520F7294 History

Now I want to generate a dynamic query in the following format (to be used with PIVOT)

 SELECT 
  [6B93119B-263B-4FED-AA89-198D26A3A3C4] DOB,

  [E27DBA94-F387-460A-BC02-84878692BDF6] Sex,

  [ABF3B85C-0DEA-44FE-857A-AC63520F7294] History

How can I do it in SQL Server 2008?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

二手情话 2024-12-22 13:22:13

DECLARE @query VARCHAR(MAX)
SET @query = 'SELECT '

SELECT @query = @query + '[' + CONVERT(NVARCHAR(50),AttributeID) + '] ' + AttributeName + ',' 
FROM MyTable

PRINT @query

DECLARE @query VARCHAR(MAX)
SET @query = 'SELECT '

SELECT @query = @query + '[' + CONVERT(NVARCHAR(50),AttributeID) + '] ' + AttributeName + ',' 
FROM MyTable

PRINT @query

回复收藏 0 原文

余厌 2024-12-22 13:22:13

以下代码产生了我想要的结果：

DECLARE @Query VARCHAR(MAX);

SET @Query = 'SELECT ';

WITH Data AS (
    SELECT '6B93119B-263B-4FED-AA89-198D26A3A3C4' AS AttributeID, 'DOB' AS AttributeName

    UNION ALL

    SELECT 'E27DBA94-F387-460A-BC02-84878692BDF6', 'Sex'

    UNION ALL

    SELECT 'ABF3B85C-0DEA-44FE-857A-AC63520F7294', 'History'
)
SELECT @Query = @Query + '''' + AttributeID + ''' ' + AttributeName + ', '
FROM Data

SET @Query = SUBSTRING(@Query, 1, LEN(@Query) - 1);

SELECT @Query

编辑： 需要明确的是，上面示例中以“WITH Data AS (”开头并以右括号 (“)”结尾的 SQL 部分是只是作为样本数据。您可以完全省略它，因此示例变为：

DECLARE @Query VARCHAR(MAX);

SET @Query = 'SELECT ';

SELECT @Query = @Query + '''' + AttributeID + ''' ' + AttributeName + ', '
FROM MyTable

SET @Query = SUBSTRING(@Query, 1, LEN(@Query) - 1);

SELECT @Query

The following produces the desired result for me:

DECLARE @Query VARCHAR(MAX);

SET @Query = 'SELECT ';

WITH Data AS (
    SELECT '6B93119B-263B-4FED-AA89-198D26A3A3C4' AS AttributeID, 'DOB' AS AttributeName

    UNION ALL

    SELECT 'E27DBA94-F387-460A-BC02-84878692BDF6', 'Sex'

    UNION ALL

    SELECT 'ABF3B85C-0DEA-44FE-857A-AC63520F7294', 'History'
)
SELECT @Query = @Query + '''' + AttributeID + ''' ' + AttributeName + ', '
FROM Data

SET @Query = SUBSTRING(@Query, 1, LEN(@Query) - 1);

SELECT @Query

Edit: Just to be clear, the section of SQL in the above sample beginning "WITH Data AS (" and ending at the close bracket (")") is just there as sample data. You can omit it entirely, so the sample becomes:

DECLARE @Query VARCHAR(MAX);

SET @Query = 'SELECT ';

SELECT @Query = @Query + '''' + AttributeID + ''' ' + AttributeName + ', '
FROM MyTable

SET @Query = SUBSTRING(@Query, 1, LEN(@Query) - 1);

SELECT @Query

回复收藏 0 原文

~没有更多了~