英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论