CODEWITHSUNDEEP
sql-server
sripriya
sripriya

Reputation: 131

How to create auto increment Column Based on the value

IN SQL server i have table called Table Employee . in Employee talbe have one Column Field Name Dependennt Name Like Table .In this table Employee Dependent on others. Dependvalue values Comes repeat many Times Dpendent Column.

Employee Name                         DependName  
Kelly, Sean                           Elieen Kelly 
Kelly, Sean                           Sienna Kelly 
Kelly, Sean                           Elieen Kelly 
Kelly, Sean                           Sienna Kelly
Kelly, Sean                           Kevin  Kelly
Kelly, Sean                           Mark  Kelly 
Neuenschwander                        Spide kelly 
Neuenschwander                        sumiln Kelly

In this Table Employee Name Repeat many time and also Dependent Employee also repat many time. i have big File . i dont know How many dpendname is there SQL file. its take Long Sql file. So i want Table Like this .spilit on based on Employee name

 Employee Name        DependName1  DependName2    DependName3  DependName4
 Elieen Kelly         Elieen Kelly  Sienna Kelly  Kevin Kelly Mark Kelly 
Neuenschwander        Spide kelly   sumiln Kelly

Update :

  Employee Name        DependName1  DependName2    DependName3  DependName4
    Kelly, Sean       Elien Kelly   SieeaKelly    SieeaKelly    SieeaKelly

my table be like .in this SQL big file some more employee it may be have more DependName . how to spilit Table Like. how to create column Basesd on DependName ? thanks in advance

Upvotes: 0

Views: 624

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81960

A simple conditional aggregation in concert with Row_Number() (or Dense_Rank() to eliminate duplicates) would do the trick if you don't need to go dynamic

Declare @YourTable table (EmployeeName varchar(50), DependName  varchar(50))
Insert Into @YourTable values
('Kelly, Sean'   ,'Elieen Kelly'), 
('Kelly, Sean'   ,'Sienna Kelly'), 
('Kelly, Sean'   ,'Elieen Kelly'), 
('Kelly, Sean'   ,'Sienna Kelly'),
('Kelly, Sean'   ,'Kevin  Kelly'),
('Kelly, Sean'   ,'Mark  Kelly'), 
('Neuenschwander','Spide kelly'), 
('Neuenschwander','sumiln Kelly') 


Select EmployeeName
      ,DependName1  = max(case when RN=1 then DependName else '' end)
      ,DependName2  = max(case when RN=2 then DependName else '' end)
      ,DependName3  = max(case when RN=3 then DependName else '' end)
      ,DependName4  = max(case when RN=4 then DependName else '' end)
 From  (
            Select *,RN=Dense_Rank() over (Partition By EmployeeName Order by DependName)
             From  @YourTable
       ) A
 Group By EmployeeName

Returns

EmployeeName    DependName1     DependName2     DependName3   DependName4
Kelly, Sean     Elieen Kelly    Kevin  Kelly    Mark  Kelly   Sienna Kelly
Neuenschwander  Spide kelly     sumiln Kelly

Upvotes: 1

Related Questions