Merge duplicate rows and create multiple columns based on data from one column

Question

I am trying to merge a list of accounts from different sources and identify which account codes are set up identically across all sources.

Here is the code:

SELECT
    Distinct a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
FROM 700_AccountTable a
Left join 700_AccountDetail b
    on a.MainCode = b.MainCode
Left join 700_AccountHistory c
    on a.AccountKey = c.AccountKey
Where 
    b.Status = 'Active'
GROUP BY
    a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
HAVING
    count(c.AccountKey) > 0


UNION


SELECT
    Distinct a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
FROM 111_AccountTable a
Left join 111_AccountDetail b
    on a.MainCode = b.MainCode
Left join 111_AccountHistory c
    on a.AccountKey = c.AccountKey
Where 
    b.Status = 'Active'
GROUP BY
    a.MainCode,
    a.Type,
    a.Group,
    a.InternalCode
HAVING
    count(c.AccountKey) > 0

Here is the current output:

MainCode	Type	Group	InternalCode
3010	11	3000	700
3010	11	3000	111
3020	11	3000	700
3020	11	3000	111
3030	11	3000	111
3070	11	3000	111
3070	11	3000	700
3070	12	3000	700

This is the desired output:

MainCode	Type	Group	700	111
3010	11	3000	X	X
3020	11	3000	X	X
3030	11	3000		X
3070	11	3000	X	X
3070	12	3000	X

Answer 1

Your current query has syntax error. As group is a reserved keyword in SQL; While using it in sql server as a column name; surround it using square brackets.

Using your original query and replacing Group by [Group]

We can PIVOT the InternalCode column as follows :

select 
    a.MainCode, 
    a.Type, 
    a.[Group],
    max(case when a.InternalCode = '700' then 'X' ELSE '' end) AS '700',
    max(case when a.InternalCode = '111' then 'X' ELSE '' end) AS '111'
from (
    select 
        a.MainCode, 
        a.Type, 
        a.[Group], 
        a.InternalCode
    from 700_AccountTable a
    left join 700_AccountDetail b on a.MainCode = b.MainCode
    left join 700_AccountHistory c on a.AccountKey = c.AccountKey
    where b.Status = 'Active'
    union 
    select 
        a.MainCode, 
        a.Type, 
        a.[Group],
        a.InternalCode
    from 111_AccountTable a
    left join 111_AccountDetail b on a.MainCode = b.MainCode
    left join 111_AccountHistory c on a.AccountKey = c.AccountKey
    where b.Status = 'Active'
) a
group by 
    a.MainCode, 
    a.Type, 
    a.[Group];

As I don't have your original data; I used the result of your union query to create a table and work on.

Here is a working solution using SQLFIDDLE

This returns the expected result:

MainCode	Type	Group	700	111
3010	11	3000	X	X
3020	11	3000	X	X
3030	11	3000		X
3070	11	3000	X	X
3070	12	3000	X

You can also do it using PIVOT function in SQL Server as :

select * from (
select MainCode, Type, [Group], InternalCode, 'Y' as Value
from (
    select 
        a.MainCode, 
        a.Type, 
        a.[Group], 
        a.InternalCode
    from 700_AccountTable a
    left join 700_AccountDetail b on a.MainCode = b.MainCode
    left join 700_AccountHistory c on a.AccountKey = c.AccountKey
    where b.Status = 'Active'
    union 
    select 
        a.MainCode, 
        a.Type, 
        a.[Group],
        a.InternalCode
    from 111_AccountTable a
    left join 111_AccountDetail b on a.MainCode = b.MainCode
    left join 111_AccountHistory c on a.AccountKey = c.AccountKey
    where b.Status = 'Active'
) as a
pivot (
max(Value) for InternalCode in ([700], [111])
) as pvt_column
order by MainCode, Type, [Group];

Here is a working demo using your current table data :

Answer 2

use a pivot table with the InternalCode values as columns. You can make this into a dynamic sql and gather all the values for InternalCode as the columns using a string then execute the sql string

SELECT * FROM (
SELECT MainCode, Type, Group, InternalCode, 'Y' AS Value
FROM table1
) AS t
PIVOT (
MAX(Value) FOR InternalCode IN ([700], [111])
) AS pvt
ORDER BY MainCode, Type, Group

Answer 3

You have your data spread over two different tables, with possibly overlaping accounts, and you want one row per account. This suggests a full join rather than union:

SELECT 
    COALESCE(a700.MainCode, a111.MainCode) MainCode,
    COALESCE(a700.Type, a111.Type) Type,
    COALESCE(a700.Group, a111.Group) Group,
    CASE WHEN a700.MainCode IS NOT NULL THEN 'X' END Code700,
    CASE WHEN a111.MainCode IS NOT NULL THEN 'X' END Code111
FROM (
    SELECT a.MainCode, a.Type, a.Group
    FROM 700_AccountTable a
    INNER JOIN 700_AccountDetail  ad ON ad.MainCode = a.MainCode
    INNER JOIN 700_AccountHistory ah ON a.AccountKey = ah.AccountKey
    WHERE ad.Status = 'Active'
    GROUP BY a.MainCode, a.Type, a.Group, a.InternalCode
) a700
FULL JOIN (
    SELECT a.MainCode, a.Type, a.Group
    FROM 111_AccountTable a
    INNER JOIN 111_AccountDetail  ad ON ad.MainCode = a.MainCode
    INNER JOIN 111_AccountHistory ah ON a.AccountKey = ah.AccountKey
    WHERE ad.Status = 'Active'
    GROUP BY a.MainCode, a.Type, a.Group, a.InternalCode
) a111 ON a111.MainCode = a700.MainCode AND a111.Type = a700.Type AND a111.Group = a700.Group

I performed a few changes to your original queries:

• DISTINCT is superfluous in your aggregate query
• the LEFT JOINs resolve to INNER JOINs, since you have a condition in the WHERE clause that relates to a left-side table
• the HAVING clause is superfluous, since the columns it counts is used in a join condition, it cannot be NULL (so the count is always greater than 0)
• meaningful table aliases make the query easier to follow

The upside of this approach is that this there is no aggregation (or pivot, which is aggregation in essence) in the outer query; the database only needs to join the results of the two intermediate steps.