SQL Server How to add an alias to a value inside a pivot [closed]

ioekq8ef 于 2023-03-07 发布在其他

关注(0)|答案(1)|浏览(164)

Closed. This question is not reproducible or was caused by typos . It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 3 days ago.
Improve this question

I'm having trouble creating an Alias within a Pivot based on the values as below.

SELECT *
FROM (
    SELECT trntypeid, trnstatusid, errorcodeid
    FROM trndatacore 
    WHERE companyid = 374 
        AND trntypeid = 7 
        AND trndatetime BETWEEN '2023-01-01' AND '2023-03-01' 
        AND trnstatusid IN (11,13)
) AS Sourcetable
PIVOT (
    COUNT(errorcodeid)
    FOR errorcodeid IN ([300] as Invalid BSB, [301] as GeneralDecline)
) AS ClientReport

When I run this query I receive a syntax error for the alias within the FOR statement of the pivot. I've tried adding quotes to the alias names, square brakcets however I don't believe this to be the issue.

Any help would be greatly appreciated

sql-server

来源：https://stackoverflow.com/questions/75621721/how-to-add-an-alias-to-a-value-inside-a-pivot

1条答案

按热度按时间

oxcyiej71#

It's really helpful to provide example DML and DDL when you ask a question. From your question I mocked some up for you:

DECLARE @trndatacore  TABLE (CompanyID INT, trntypeid INT, trndatetime DATETIME, trnstatusid TINYINT, errorcodeid INT)
INSERT INTO @trndatacore (CompanyID, trntypeid, trndatetime, trnstatusid, errorcodeid) VALUES
(374, 7, '2023-01-01', 11, 300), (374, 7, '2023-01-01', 13, 301), (374, 7, '2023-01-01', 13, 300), (374, 7, '2023-01-01', 11, 301);

Using that data, here's an example of how to apply an alias to a pivoted column:

SELECT trntypeid, trnstatusid, [300] AS InvalidBSB, [301] AS GeneralDecline
FROM (
    SELECT trntypeid, trnstatusid, errorcodeid
    FROM @trndatacore 
    WHERE companyid = 374 
        AND trntypeid = 7 
        AND trndatetime BETWEEN '2023-01-01' AND '2023-03-01' 
        AND trnstatusid IN (11,13)
) AS Sourcetable
PIVOT (
    COUNT(errorcodeid)
    FOR errorcodeid IN ([300], [301])
) AS ClientReport

trntypeid	trnstatusid	InvalidBSB	GeneralDecline
7	11	1	1
7	13	1	1

The trick to it is doing it in the outer part of the select, when the pivoted columns are available by their values. In this case those values are illegal (you can't start with a number) so they must also be escaped.

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server How to add an alias to a value inside a pivot [closed]

1条答案

相关问题

热门标签

最新问答