SQL Query for typical scenario based on multiple conditions

Transact-SQL
1

Hi All,

I have table like below,Each employee id has data in multpile countries.I have to set Country1 code always defalut to USA and remaining countries i have to select the country names in ascending order and assign to country code 2,3,4 based on assending order.if emp id dont have data for USA also always default it to Country1 code as USA and remaining country codes assign based on ascending values for each country.

Table:

EMPID COUNTRY
1 USA
1 UK
1 INDIA
1 AUSTRALIA
2 USA
3 INDIA
3 SINGAPORE
3 MALAYSIA
4 DUBAI
4 UK
4 AUSTRALIA
5 UK

OUTPUT:

EMPID COUNTRY1 COUNTRY2 COUNTRY3 COUNTRY4
1 USA AUSTRALIA INDIA UK
2 USA null null null
3 USA INDIA MALAYSIA SINGAPORE
4 USA AUSTRALIA DUBAI UK
5 USA UK null null

sample script:

CREATE TABLE COUNTRY (
EMPID int,
Country varchar(255)
);

INSERT INTO COUNTRY VALUES ('1', 'USA');
INSERT INTO COUNTRY VALUES ('1', 'UK');
INSERT INTO COUNTRY VALUES ('1', 'INDIA');
INSERT INTO COUNTRY VALUES ('1', 'AUSTRALIA');
INSERT INTO COUNTRY VALUES ('2', 'USA');
INSERT INTO COUNTRY VALUES ('3', 'INDIA');
INSERT INTO COUNTRY VALUES ('3', 'SINGAPORE');
INSERT INTO COUNTRY VALUES ('3', 'MALAYSIA');
INSERT INTO COUNTRY VALUES ('4', 'DUBAI');
INSERT INTO COUNTRY VALUES ('4', 'UK');
INSERT INTO COUNTRY VALUES ('4', 'AUSTRALIA');
INSERT INTO COUNTRY VALUES ('5', 'UK');

2

Try this:

select p.empid
      ,p.[1] as country1
      ,p.[2] as country2
      ,p.[3] as country3
      ,p.[4] as country4
  from (select empid
              ,country
              ,row_number() over(partition by empid order by country)+1 as rn
          from country
         where country<>'USA'
        union all
        select empid
              ,'USA'
              ,1
          from country
         group by empid
       ) as c
 pivot (min(country)
        for rn in ([1],[2],[3],[4])
       ) as p
;