Sql Pivot horizontal

Question

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

nah not always, this was just an example. ID could have x amount of names. Sorry should've specified that — aofe1337, Jun 27 '16 at 09:18
so what is the expected output when id has X amount of names ? — TheGameiswar, Jun 27 '16 at 09:19
Have a look at this question http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Pரதீப், Jun 27 '16 at 09:20
expected output for x amount of names would be ID name name name name name name and so forth Thanks will have a read of the link you have provided — aofe1337, Jun 27 '16 at 09:22
"without an aggregate as there are alphanumeric values" - but you _can_ take the `max` or `min` of a string, which ultimately means you just get it back. I'm not sure whether it would suffice for this case, though. — underscore_d, Jun 27 '16 at 13:05
in your `ID` can `Bravo` be repeated like `'1Bravo', '5Bravo'`? are you looking for rows containing `'Bravo'` or rows with `ID = '1Bravo'`? — MtwStark, Jun 28 '16 at 09:17

score 0 · Answer 1 · answered Jun 27 '16 at 18:42

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

--@Id = '1Bravo` result
--Id       Name  Name
---------- ----- -----
--1Bravo   John  Smith

--@Id = '2Charlie` result
--Id       Name  Name  Name
---------- ----- ----- -----
--2Charlie Billy Bobby Dave

MtwStark · Answer 2 · 2016-07-08T11:27:56.087

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

would like to know the reason to mark my answer "not useful" — MtwStark, Jun 28 '16 at 07:45

Sql Pivot horizontal

2 Answers2