Int for String上的SQL Pivot [英] SQL Pivot on Int for String

问题描述

尝试使用Pivot操作某些SQL，但是我需要透视的是文本，我无法使其正常工作.

Trying to manipulate some SQL with a Pivot however what I need to pivot on is text and I cant just get it working.

customerID  appointment             appointmentNumber   treatment
9           2014-01-09 09:30:00.000 1                   Osteo F 45
9           2014-01-20 09:00:00.000 2                   Osteo F 45
9           2014-01-30 09:30:00.000 3                   Osteo F 45
9           2014-02-10 09:00:00.000 4                   Osteo F 45

我需要将"appointmentNumber"列转换为列标题，并在下面显示处理" ...

What I need is to turn the column "appointmentNumber" into column headers, with the "treatment" shown below...

customerID 1 2 3 4 etc...

过去，我一直在数据串上使用数据透视表来存储数字(总和，计数)，而在字符串上从未使用过数据透视表，所以我有点迷失了.

I have always used Pivot tables on strings for numbers (sums, counts) in the past and never on numbers for strings so I am a little lost.

我查看了带字符串的SQL Pivot ，但似乎无法将其应用于我的自己的代码.

I had a look at SQL Pivot with String but cant seem to apply this to my own code.

有什么想法吗?

推荐答案

您可以在字符串列的max/min集合函数之间进行选择.在这种情况下，我假设约会编号，客户编号是唯一的，因此聚合函数除了采用第一个值外，实际上没有做任何事情.

You can choose between max/min aggregate functions for a string column. In this case I'd assume appointmentNumber, customerId are unique so the aggregate function isn't really doing anything except taking the first value.

select
    *
from
    table t
    pivot (
        max(treatment)
        for appointmentNumber in ([1],[2],[3],[4],[5])
    ) p

这篇关于Int for String上的SQL Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！