根据年月拼接的动态枢纽

huangapple go评论58阅读模式
英文:

Dynamic pivot by year month concatenated

问题

I can help you with the translation of the provided text. Here's the translated content:

我在表格中有以下数据。每个“LocationID”都会有很多,对于每个位置,会有120个月(固定为10年)的数据。

|位置|年|月|金额|
|:---|:---|:---|:---|
|101|2023|4|65.45|
|101|2023|5|65.45|
|101|2023|6|65.45|
|101|2023|7|65.45|
|101|2023|8|65.45|
|101|2023|9|65.45|
|101|2023|10|65.45|
|101|2023|11|65.45|
|101|2023|12|65.45|
|101|2024|1|65.45|
|101|2024|2|65.45|
|101|2024|3|65.45|
|101|2024|4|34.12|
|101|2024|5|34.12|
|101|2024|6|34.12|

我尝试以以下格式获取它(按年月升序排列):

|位置|2023年4月|2023年5月|2023年6月|2023年7月|2023年8月|2023年9月|2023年10月|2023年11月|2023年12月|2024年1月|.....
|:------|:--|:------|:---|:------|:--|:------|:---|:------|:--|:------|:---|
|101|65.45|65.45|65.45|65.45|65.45|65.45|65.45|65.45|65.45|65.45|

我已经开始使用以下查询来合并日期和月份:

SELECT
loc, CAST(yr AS varchar) + '-' + DATENAME(mm, DATEADD(mm, Mon, -1)) [期间],
金额
FROM
TBL
WHERE
loc = 101 AND plID = 11
ORDER BY
年, 月


这将返回以下结果:

|位置|期间|金额
|:------|:--|:------|
|101|2023年4月|65.45|
|101|2023年8月|65.45|
|101|2023年12月|65.45|
|101|2023年7月|65.45|

需要一些帮助来找出将数据从这个格式/布局转换为我上面提到的期望格式的最佳方法...

有什么建议吗?

Please note that I've translated the text without the code parts, as you requested. If you need any further assistance or have specific questions about the content, feel free to ask.

英文:

I have the following data in a table. There will be many LocationID and for each location, there will be 120 months (fixed 10 years) of data.

LOC Yr Mon Amt
101 2023 4 65.45
101 2023 5 65.45
101 2023 6 65.45
101 2023 7 65.45
101 2023 8 65.45
101 2023 9 65.45
101 2023 10 65.45
101 2023 11 65.45
101 2023 12 65.45
101 2024 1 65.45
101 2024 2 65.45
101 2024 3 65.45
101 2024 4 34.12
101 2024 5 34.12
101 2024 6 34.12

I'm trying to get it in a format like the following (ascending year-month order):

LOC 2023-Apr 2023-May 2023-Jun 2023-Jul 2023-Aug 2023-Sep 2023-Oct 2023-Nov 2023-Dec 2024-Jan .....
101 65.45 65.45 65.45 65.45 65.45 65.45 65.45 65.45 65.45 65.45

I got started with the following query to combine the date and month

SELECT 
    loc, CAST(yr AS varchar) + '-' + DATENAME(mm, DATEADD(mm, Mon, -1)) [Period], 
    Amt
FROM 
    TBL 
WHERE 
    loc = 101 AND plID = 11
ORDER BY
    Yr, Mon

Which returns this result:

loc Period Amt
101 2023-April 65.45
101 2023-August 65.45
101 2023-December 65.45
101 2023-July 65.45

Needing some help figure out the best approach to get the data from this to the desired format/layout that I mentioned in the second table above..

Suggestions?

答案1

得分: 1

以下是您要翻译的代码部分:

我提出了以下查询,它有效。由于在第二个选择(@query)中无法使用CTE,我不得不将数据放入临时表中!另外,列未按我想要的方式排序。

有什么建议吗?

SELECT loc, cast(yr as varchar) + '-' + DateName(mm,DATEADD(mm,Mon,-1)) [Period], Amt into #TempTbl FROM TBL WHERE loc = 101 and plID = 11

DECLARE @cols AS VARCHAR(MAX),
    @query  AS VARCHAR(MAX);
	
pivot 
(
     min(Amt)
    for Period in (' + @cols + ')
) p '

SELECT loc, cast(yr as varchar) + '-' + DateName(mm,DATEADD(mm,Mon,-1)) [Period], Amt into #TempTbl FROM TBL WHERE loc = 101 and plID = 11

DECLARE @cols AS VARCHAR(MAX),
    @query  AS VARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Period]) 
        FROM #TempTbl c
        FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT LOC, ' + @cols + ' from 
        (
            select LOC
                , Amt
                , [Period]
            from #TempTbl
       ) x
        pivot 
        (
             min(Amt)
            for Period in (' + @cols + ')
        ) p '

execute(@query)
英文:

I came up with the following query which works. I had to put the data in a temp table as the CTE was not available in the second select (@query)! Also, the columns are not sorted in the way I want.

Suggestions?

SELECT loc, cast(yr as varchar) + '-' + DateName(mm,DATEADD(mm,Mon,-1)) [Period], Amt into #TempTbl FROM TBL WHERE loc = 101 and plID = 11


DECLARE @cols AS VARCHAR(MAX),
    @query  AS VARCHAR(MAX);
	

            pivot 
            (
                 min(Amt)
                for Period in (' + @cols + ')
            ) p '
			
			
SELECT loc, cast(yr as varchar) + '-' + DateName(mm,DATEADD(mm,Mon,-1)) [Period], Amt into #TempTbl FROM TBL WHERE loc = 101 and plID = 11


DECLARE @cols AS VARCHAR(MAX),
    @query  AS VARCHAR(MAX);
	

SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Period]) 
            FROM #TempTbl c
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT LOC, ' + @cols + ' from 
            (
                select LOC
                    , Amt
                    , [Period]
                from #TempTbl
           ) x
            pivot 
            (
                 min(Amt)
                for Period in (' + @cols + ')
            ) p '

execute(@query)

huangapple
  • 本文由 发表于 2023年5月26日 00:00:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76334252.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定