T-SQL Find Values Between Min and Max at specific Index

Question

I have a table with 2 values: MAC and ID.

One ID can have multiple MAC's. For Example:

I added a column Count, which counts the number of ID's:

My end goal is to have the MAC's split up like this:

I THINK I'm on the right track using case statements:

select count, ID,
MAC1 = case
when count >0 then min(MAC) end,
MAC2 = case 
when count = 2 then max(MAC) end,
MAC3 = case
when count = 3 then max(MAC) end,
MAC4 = case
when count = 4 then max(MAC) end from MACTABLE

But that obviously gives me results like this:

So my question is this: How do I get MAC2, MAC3, etc if the count is more than 2? Is there a selected index type function? Am I way overthinking this? The MAC's don't need to be in a specific order, that's just the best way that I could think to do this. Any help is appreciated.

Answer 1

CREATE TABLE [MAC$] (
    [ID] int,
    [MAC] varchar(10)
)
INSERT [dbo].[MAC$] ([ID], [MAC]) VALUES (1, N'120034')
INSERT [dbo].[MAC$] ([ID], [MAC]) VALUES (1, N'567869')
INSERT [dbo].[MAC$] ([ID], [MAC]) VALUES (1, N'741AB00')
INSERT [dbo].[MAC$] ([ID], [MAC]) VALUES (2, N'185AZ1')
INSERT [dbo].[MAC$] ([ID], [MAC]) VALUES (2, N'FD8978')
INSERT [dbo].[MAC$] ([ID], [MAC]) VALUES (3, N'H85HLK')
GO
SELECT * FROM
(SELECT Id, CAST([Mac] as varchar(10)) as val, 'MAC_' + CAST(DENSE_RANK() OVER (PARTITION BY Id ORDER BY Id, Mac) as varchar(10)) as namePivot FROM [Mac$] WHERE LEN(Mac)>0) as data
PIVOT (MAX(val) FOR namePivot in ([MAC_1],[MAC_2],[MAC_3],[MAC_4],[MAC_5])) as P
GROUP BY Id,[MAC_1],[MAC_2],[MAC_3],[MAC_4],[MAC_5]

Recommended way for expanding it out to MAC_N:

DECLARE @sColMac varchar(MAX);
DECLARE @iNumMac int;
SET @iNumMac = 1;
WHILE @iNumMac <= 5
    BEGIN
    SELECT @sColMac = COALESCE(@sColMac + ',', '') + QUOTENAME('MAC_' + CONVERT(varchar(10),@iNumMac))
    SET @iNumMac = @iNumMac + 1
    END

SELECT @sColMac

Answer 2

SELECT * 
FROM

    (
    select case when x.COUNTID = 1 then 'MAC1' when x.COUNTID = 2 then 'MAC2' when x.COUNTID = 3 then 'MAC3' when x.COUNTID = 4 then 'MAC4' end MAC
            ,ID
            ,COUNTID
    from
        ( -- YOUR ORIGINAL (COUNT) QUERY-----
            select mac
                    ,ID
                    ,count(ID) COUNTID
            from YOUR_TABLE
            group by mac, ID
        ) x
    ) SRC
pivot(
    sum(COUNTID)
    for mac in ([MAC1], [MAC2], [MAC3], [MAC4])) piv