如何更改包含数据数组的变量的顺序 [英] How to change order of variable that contains array of data

问题描述

Iam使用pivot for columns编写存储过程并将列分配给变量

@ sqlquery1 = jan2015，feb2015，march2015

@sqlquery2 = jan-Transferdate， feb-transferdate，mar-transferdate

Iam在select语句中调用这两个变量，比如'+ @sqlquery1 +'，'+ @ sqlquery2 +'但是就像

jan2015，feb2015 ，march2015，jan-Transferdate，feb-transferdate，mar-transferdate

但是我需要点赞

jan2015，jan-Transferdate，feb2015，feb-transferdate ,, march2015 ，mar-transferdate



我的尝试：



我试过通过一个接一个地调用变量

Iam writing stored procedure in that iam using pivot for columns and assign columns to variable
@sqlquery1 = jan2015,feb2015,march2015
@sqlquery2 = jan-Transferdate,feb-transferdate,mar-transferdate
Iam calling this two variable in select statement like '+@sqlquery1+','+@sqlquery2+' but out came like
jan2015,feb2015,march2015,jan-Transferdate,feb-transferdate,mar-transferdate
but i need out put like
jan2015,jan-Transferdate,feb2015,feb-transferdate,,march2015,mar-transferdate

What I have tried:

I tried by calling variable one after another

推荐答案

我假设列的顺序很重要。以报告为例？而且您正在使用动态SQL来创建对象？为了能够对列进行排序，您需要能够链接用于创建列的值。



您可以使用查找表为每个列名分配一个月和/或一年。例如：

I assume that the order of the columns is significant. For a report as an example? And that you are using dynamic SQL to create an object? To be able to order the columns you need to be able to link the values you are using to create the columns.

You could use a look up table that assigns a month and/or year to each column name. For example:

CREATE TABLE lkupDateValue (
    Month INT
    ,Year INT
    ,Value NVARCHAR(100)
    )
GO;

INSERT INTO lkupDateValue (
    Month
    ,Year
    ,Value
    )
VALUES (1,2015,'jan2015')
    ,(1,2015,'jan-Transferdate')
    ,(2,2015,'feb2015')
    ,(2,2015,'feb-Transferdate')
    ...

当您连接字符串时，您可以使用表值拆分函数提供t列表他重视。然后可以将其连接到月份和/或年份订购的lkupDateValue表。此查询可用于使用STUFF函数创建分隔列表。参见示例：

When you have concatenated the strings you can use a table-valued split function to provide a list of the values. This can then be joined on to lkupDateValue table ordered on the month and/or year. This query can be used to create a delimited list using the STUFF function. See example:

SELECT STUFF(
    (SELECT ',' + S1.splitdata 
    FROM FNT_SplitTableFunction(@sqlquery1+','+@sqlquery2) S1
    INNER JOIN lkupDateValue S2
        ON S1.splitdata = S2.Value
    ORDER BY S2.[Year]
	,S2.[Month]
    FOR XML PATH (''))
    , 1, 1, '')

STUFF函数的输出可以填充变量，也可以根据需要用作子查询。



这是表格拆分的链接功能。



它不优雅，可能不是我通常会如何处理解决方案，但是你提供的信息有限它会完成这项工作。

The the output from the STUFF function can populate a variable or be used as a subquery depending on your needs.

Here is a link to a table split function.

It's not elegant, and probably not how I would normally approach a solution, but with the limited information you have provided it would do the job.

这篇关于如何更改包含数据数组的变量的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！