Transpose rows to column without numbers to aggregate

Question

I have login credentials along with the status and URL some have multiple userids and some have one only I want to group them by employee id and transpose the data. How can I do it in SQL Server 2019?

Database:

empid name  desc uid loc status url
100   Smith AA   U1  CA  O      www
100   Smith AA   U2  CA  C      www
100   Smith BB   U3  CA  C      www
100   Smith CC   U4  NY  C      www
101   Adams BB   U5  CA  C      www
101   Adams CC   U6  NY  C      www

Desired Results

emid name  AA loc sta url BB Loc sta url CC loc url
100  Smith U1 CA  O   www U3 CA  C   www U4  NY www
100  Smith U2 CA  C   www
101  Adams                U5 CA  C   www U6 NY www 

Answer 1

You could do a dynamic solution:

SELECT  *
INTO #data
FROM    (
    VALUES  (100, N'Smith', N'AA', N'U1', N'CA', N'O', N'www')
    ,   (100, N'Smith', N'AA', N'U2', N'CA', N'C', N'www')
    ,   (100, N'Smith', N'BB', N'U3', N'CA', N'C', N'www')
    ,   (100, N'Smith', N'CC', N'U4', N'NY', N'C', N'www')
    ,   (101, N'Adams', N'BB', N'U5', N'CA', N'C', N'www')
    ,   (101, N'Adams', N'CC', N'U6', N'NY', N'C', N'www')
) t (empid,name,[desc],uid,loc,status,url)


DECLARE @table NVARCHAR(667) = '#data'
,   @ids nvarchar(max) = '[empid], [name]'
,   @group_field sysname = '[desc]'

DECLARE @fields TABLE (pv sysname, label sysname, sort int)

INSERT INTO @fields
VALUES  ('uid', '@@group@@', 1)
,   ('loc', 'loc', 2)
,   ('status', 'sta', 3)
,   ('url', 'url', 4)

-- Build groups...
CREATE TABLE #t_groups (grp nvarchar(max) COLLATE database_default)

DECLARE @sql nvarchar(max) = N'
insert into #t_groups
select distinct ' + @group_field + '
from ' + @table

EXEC    sp_executesql @sql

-- build the final sql
SET @sql = '
    select ' + @ids + ', ' 

SELECT  @sql = @sql + STRING_AGG(CONCAT('max(case when ' , @group_field,'= ',quotename(grp, ''''), ' then ', QUOTENAME(f.pv), ' end', ') as ', REPLACE(f.label, '@@group@@', QUOTENAME(t.grp))), ',') WITHIN GROUP(ORDER BY t.grp, f.sort)
FROM    #t_groups t
CROSS JOIN @fields f

/*
-- Older version without string_agg:

SELECT  @sql = @sql + STUFF(
    (
    SELECT CONCAT(',max(case when ' , @group_field,'= ',quotename(grp, ''''), ' then ', QUOTENAME(f.pv), ' end', ') as ', REPLACE(f.label, '@@group@@', QUOTENAME(t.grp)))
    FROM    #t_groups t
    CROSS JOIN @fields f
    ORDER BY t.grp, f.sort
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
*/ 
SET @sql = @sql + '
from (
    select *
    , row_number() over(partition by ' + @ids + ',' + @group_field + 'order by @@spid) as sort
    from ' + @table + '
    ) t
group by ' + @ids + ', sort'

--SELECT    @sql

EXEC    (@sql)

I created some variables that holds the information you want to group by etc.

@ids contains list of columns that should identify a unique row @group_field is the pivoting field that controls how the columns are pivoted

@fields are the pivoted fields table which maps the fields to a label.

For dynamic pivots one needs to get a list of groups, which i do by populating the #t_groups table. Then, the rest of the code builds the whole thing together and finally executes the dynamic string. You can use print to figure out the final string.

Output is:

empid	name	AA	loc	sta	url	BB	loc	sta	url	CC	loc	sta	url
100	Smith	U1	CA	O	www	U3	CA	C	www	U4	NY	C	www
100	Smith	U2	CA	C	www	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
101	Adams	NULL	NULL	NULL	NULL	U5	CA	C	www	U6	NY	C	www