How to group duplicate records and set new columns/ values based on conditions

Question

I would like to group duplicate records by the column [FIELD] and add new columns based on the given conditions.

Sample table:

CREATE TABLE [dbo].[TEST](
    [TYPE] [nvarchar](255) NULL,
    [SECTION] [nvarchar](255) NULL,
    [FIELD] [nvarchar](255) NULL,
    [InREPO] [nvarchar](255) NULL)

INSERT INTO [dbo].[TEST]
           ([TYPE]
           ,[SECTION]
           ,[FIELD]
           ,[InREPO])
     VALUES
           ('NDA','Info','Counterparty','TRUE'),
           ('NDA','Info','Country','TRUE'),
           ('NDA','Action','Region','FALSE'),
           ('CIS','Info','Counterparty','TRUE'),
           ('CIS','Action','Country','FALSE'),
           ('CIS','Action','Region','TRUE'),
           ('CIS','Hidden','Address','FALSE')

TYPE	SECTION	FIELD	InREPO
NDA	Info	Counterparty	TRUE
NDA	Info	Country	TRUE
NDA	Action	Region	FALSE
CIS	Info	Counterparty	TRUE
CIS	Action	Country	FALSE
CIS	Action	Region	TRUE
CIS	Hidden	Address	FALSE

Expected result:

FIELD	NDA	NDA_SECTION	NDA_InREPO	CIS	CIS_SECTION	CIS_InREPO
Counterparty	TRUE	Info	TRUE	TRUE	Info	TRUE
Country	TRUE	Info	TRUE	TRUE	Action	FALSE
Region	TRUE	Action	FALSE	TRUE	Action	TRUE
Address	FALSE	n/a	n/a	TRUE	Hidden	FALSE

What I have so far:

    SELECT [FIELD],
       CASE
           WHEN [TYPE] like 'NDA' THEN
               'TRUE'
           ELSE
               'FALSE'
       END AS [NDA],
       CASE
           WHEN [TYPE] like 'NDA' THEN
               [SECTION]
           ELSE
               'n/a'
       END AS [NDA_SECTION],
       CASE
           WHEN [TYPE] like 'NDA' THEN
               [InREPO]
           ELSE
               'n/a'
       END AS [NDA_InREPO],
       CASE
           WHEN [TYPE] like 'CIS' THEN
               'TRUE'
           ELSE
               'FALSE'
       END AS [CIS],
       CASE
           WHEN [TYPE] like 'CIS' THEN
               [SECTION]
           ELSE
               'n/a'
       END AS [CIS_SECTION],
       CASE
           WHEN [TYPE] like 'CIS' THEN
               [InREPO]
           ELSE
               'n/a'
       END AS [CIS_InREPO]
FROM [TEST]

Current Result:

FIELD	NDA	NDA_SECTION	NDA_InREPO	CIS	CIS_SECTION	CIS_InREPO
Counterparty	TRUE	Info	TRUE	FALSE	n/a	n/a
Country	TRUE	Info	TRUE	FALSE	n/a	n/a
Region	TRUE	Action	FALSE	FALSE	n/a	n/a
Counterparty	FALSE	n/a	n/a	TRUE	Info	TRUE
Country	FALSE	n/a	n/a	TRUE	Action	FALSE
Region	FALSE	n/a	n/a	TRUE	Action	TRUE
Address	FALSE	n/a	n/a	TRUE	Hidden	FALSE

Any ideas on how to achieve the expected result?

Answer 1

You could try to add GROUP BY on column [FIELD], then apply MAX aggregate function on the other columns.

This is modified version of your query, I've added GROUP BY, MAX function and replaced CASE expression with MS SQL's IFF function for shorter query.

SELECT [FIELD],
       MAX(IIF([TYPE] LIKE 'NDA', 'TRUE', 'FALSE')) AS [NDA],
       COALESCE(MAX(IIF([TYPE] LIKE 'NDA', [SECTION], NULL)), 'n/a') AS [NDA_SECTION],
       COALESCE(MAX(IIF([TYPE] LIKE 'NDA', [InREPO], NULL)), 'n/a') AS [NDA_InREPO],
       MAX(IIF([TYPE] LIKE 'CIS', 'TRUE', 'FALSE')) AS [CIS],
       COALESCE(MAX(IIF([TYPE] LIKE 'CIS', [SECTION], NULL)), 'n/a') AS [CIS_SECTION],
       COALESCE(MAX(IIF([TYPE] LIKE 'CIS', [InREPO], NULL)), 'n/a') AS [CIS_InREPO]
FROM [TEST]
GROUP BY [FIELD];

See demo here