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

fbcarpbf 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(132)

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?

sql-server

来源：https://stackoverflow.com/questions/75609260/how-to-group-duplicate-records-and-set-new-columns-values-based-on-conditions

2条答案

按热度按时间

eimct9ow1#

I suggest to create a temporary table to store the distinct values for the column FIELD and the do a left outer join on the original table for the each of the values on the column TYPE, from there you can do a CASE WHEN or ISNULL expression for each field based on type.

DECLARE @Fields TABLE (Field NVARCHAR(255))
INSERT INTO @Fields (Field)
SELECT DISTINCT Field
FROM Test

SELECT f.Field,
    CASE WHEN nda.Field IS NULL THEN 'FALSE' ELSE 'TRUE' END AS NDA,
    ISNULL(nda.Section, 'n/a') AS NDA_SECTION,
    ISNULL(nda.InREPO, 'n/a') AS NDA_InREPO,
    CASE WHEN cis.Field IS NULL THEN 'FALSE' ELSE 'TRUE' END AS CIS,
    ISNULL(cis.Section, 'n/a') AS CIS_SECTION,
    ISNULL(cis.InREPO, 'n/a') AS CIS_InREPO
FROM @Fields f
    LEFT OUTER JOIN Test nda ON nda.Field = f.Field
        AND nda.[Type] = 'NDA'
    LEFT OUTER JOIN Test cis ON cis.Field = f.Field
        AND cis.[Type] = 'CIS'

赞(0）回复(0）举报 2023-03-07

r1zk6ea12#

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

赞(0）回复(0）举报 2023-03-07

我来回答

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

2条答案

相关问题

热门标签

最新问答