问题描述:

Afternoon All,

Trying to figure out how to pivot without an aggregate as there are alphanumeric values. Is there another term for this?

Heres the data that I have:

Id Name

1Bravo John

1Bravo Smith

2Charlie Dave

2Charlie Billy

What I wish to achieve:

Id Name Name

1Bravo John Smith

To display the results with Bravo in it

I haven't really gotten anywhere with pivot

Select ID, Name

From Table

Pivot (

Max(ID) For ID Like ('Bravo%')

Any help is appreciated

Thanks

网友答案:

This would build the query dynamically based on the number of names for a given Id.

--Create Sample Data
CREATE TABLE myTable
    ([Id] varchar(8), [Name] varchar(5))
;
INSERT INTO myTable
    ([Id], [Name])
VALUES
    ('1Bravo', 'John'),('1Bravo', 'Smith'),    
    ('2Charlie', 'Dave'),('2Charlie', 'Billy'),('2Charlie', 'Bobby')
;


DECLARE @Id VARCHAR(8) = '1Bravo',
        @Sql NVARCHAR(MAX),
        @SelCols NVARCHAR(MAX),
        @PivCols NVARCHAR(MAX)

--Build Select and Pivot columns dynamically based on the number of Names for the given @Id
SELECT  @SelCols = COALESCE(@SelCols + ',','') + QUOTENAME(ROW_NUMBER() OVER (ORDER BY [Name])) + ' As [Name]',
        @PivCols = COALESCE(@PivCols + ',','') + QUOTENAME(ROW_NUMBER() OVER (ORDER BY [Name]))
FROM    myTable 
WHERE   [Id] = @Id


--Build the dynamic sql 
SET     @Sql = 'SELECT [Id], ' + @SelCols + ' 
                FROM (  SELECT  [Id], [Name], ROW_NUMBER() OVER (PARTITION BY [Id] ORDER BY [Name]) Rn
                        FROM    myTable 
                        WHERE   [Id] = @Id) mt
                PIVOT (
                    MAX([Name])
                    FOR Rn IN (' + @PivCols + ')) p'

--Execute the dynamic sql passing in the @Id parameter
EXECUTE sp_executesql @Sql, N'@Id VARCHAR(8)', @Id = @Id

[email protected] = '1Bravo` result
--Id       Name  Name
---------- ----- -----
--1Bravo   John  Smith

[email protected] = '2Charlie` result
--Id       Name  Name  Name
---------- ----- ----- -----
--2Charlie Billy Bobby Dave
网友答案:

You should first cap your maximum names quantity (i set 10 names in the query) and then you can pivot it.

I have added a column with the total number of names in the row (cnt) for easy loop or check without need to look for first null name.

;with
YourTable as (
    select * 
    from ( 
    values 
        ('1Bravo',      'John'),
        ('1Bravo',      'Smith'),
        ('2Charlie',    'Dave'),
        ('2Charlie',    'Billy')
    ) t (Id, Name)
),
n as (
    select id, count(distinct name) cnt 
    from YourTable
    group by id
),
x as (
    select *, ROW_NUMBER() over (partition by id order by name) idx
    from YourTable
)
select n.cnt, p.*
from x 
pivot (min(name) for idx in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p
inner join n on n.Id = p.Id

You can then filter for the desired ID

where n.id like '%bravo%'

I hope this helps

相关阅读:
Top