SQL QUERY
-
plz help me am begginner to ms sql server my question mulitple rowwise data should be display to one columnwise suppose from table1 i created column like empname,reference table2 i created column like reference and salary details join both table retrieve and display empname, reference,and all salary details for particular employee in one column table1 value -----------------------------| empname | referenceno -----------------------------| girijesh | 111 -----------------------------| ajay | 121 -----------------------------| table2 value -----------------------------------------------------| referenceno | salary -----------------------------------------------------| 111 | jan-10000 -----------------------------------------------------| 111 | feb-20000 -----------------------------------------------------| 111 | mar-30000 -----------------------------------------------------| 121 | jan-10000 -----------------------------------------------------| 121 | feb-20000 -----------------------------------------------------| so now result like below -----------------------------------------------------| empname | reference | salary -----------------------------------------------------| girijesh | 111 | jan-10000,feb-20000,mar-30000 -----------------------------------------------------| ajay | 121 | jan-10000,feb-20000 -----------------------------------------------------| i want query to display like above result
-
plz help me am begginner to ms sql server my question mulitple rowwise data should be display to one columnwise suppose from table1 i created column like empname,reference table2 i created column like reference and salary details join both table retrieve and display empname, reference,and all salary details for particular employee in one column table1 value -----------------------------| empname | referenceno -----------------------------| girijesh | 111 -----------------------------| ajay | 121 -----------------------------| table2 value -----------------------------------------------------| referenceno | salary -----------------------------------------------------| 111 | jan-10000 -----------------------------------------------------| 111 | feb-20000 -----------------------------------------------------| 111 | mar-30000 -----------------------------------------------------| 121 | jan-10000 -----------------------------------------------------| 121 | feb-20000 -----------------------------------------------------| so now result like below -----------------------------------------------------| empname | reference | salary -----------------------------------------------------| girijesh | 111 | jan-10000,feb-20000,mar-30000 -----------------------------------------------------| ajay | 121 | jan-10000,feb-20000 -----------------------------------------------------| i want query to display like above result
-
plz help me am begginner to ms sql server my question mulitple rowwise data should be display to one columnwise suppose from table1 i created column like empname,reference table2 i created column like reference and salary details join both table retrieve and display empname, reference,and all salary details for particular employee in one column table1 value -----------------------------| empname | referenceno -----------------------------| girijesh | 111 -----------------------------| ajay | 121 -----------------------------| table2 value -----------------------------------------------------| referenceno | salary -----------------------------------------------------| 111 | jan-10000 -----------------------------------------------------| 111 | feb-20000 -----------------------------------------------------| 111 | mar-30000 -----------------------------------------------------| 121 | jan-10000 -----------------------------------------------------| 121 | feb-20000 -----------------------------------------------------| so now result like below -----------------------------------------------------| empname | reference | salary -----------------------------------------------------| girijesh | 111 | jan-10000,feb-20000,mar-30000 -----------------------------------------------------| ajay | 121 | jan-10000,feb-20000 -----------------------------------------------------| i want query to display like above result
The function you need is called
PIVOT. See e.g. http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx[^] -
plz help me am begginner to ms sql server my question mulitple rowwise data should be display to one columnwise suppose from table1 i created column like empname,reference table2 i created column like reference and salary details join both table retrieve and display empname, reference,and all salary details for particular employee in one column table1 value -----------------------------| empname | referenceno -----------------------------| girijesh | 111 -----------------------------| ajay | 121 -----------------------------| table2 value -----------------------------------------------------| referenceno | salary -----------------------------------------------------| 111 | jan-10000 -----------------------------------------------------| 111 | feb-20000 -----------------------------------------------------| 111 | mar-30000 -----------------------------------------------------| 121 | jan-10000 -----------------------------------------------------| 121 | feb-20000 -----------------------------------------------------| so now result like below -----------------------------------------------------| empname | reference | salary -----------------------------------------------------| girijesh | 111 | jan-10000,feb-20000,mar-30000 -----------------------------------------------------| ajay | 121 | jan-10000,feb-20000 -----------------------------------------------------| i want query to display like above result
select t1.empname,t1.referenceno,t2.salary from table1 t1 join table2 t2 on t1.referenceno=t2.refereceno
-
select t1.empname,t1.referenceno,t2.salary from table1 t1 join table2 t2 on t1.referenceno=t2.refereceno
its show answer like below -----------------------------------| empname | reference | salary -----------------------------------| girijesh | 111 | jan-10000 -----------------------------------| girijesh | 111 | feb-20000 -----------------------------------| girijesh | 111 | mar-30000 -----------------------------------| ajay | 121 | jan-10000 -----------------------------------| ajay | 121 feb-20000 -----------------------------------| this wat if i executed ur query i got but i want reslut like below see ur ouput and wat am accepted output so now result like below -----------------------------------------------------| empname | reference | salary -----------------------------------------------------| girijesh | 111 | jan-10000,feb-20000,mar-30000 -----------------------------------------------------| ajay | 121 | jan-10000,feb-20000 -----------------------------------------------------| i want query to display like above result
-
in query u put case statement 1,2,3 like that but problem is reference number should chage every time wat to do ... then how to retrieve the data..
-
The function you need is called
PIVOT. See e.g. http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx[^]plz gothrough it and plz understand my concept and solve it.. bcoz u not properly understand my output
-
in query u put case statement 1,2,3 like that but problem is reference number should chage every time wat to do ... then how to retrieve the data..
Read the section on 'Concatenating values when the number of items is not known'.
-
its show answer like below -----------------------------------| empname | reference | salary -----------------------------------| girijesh | 111 | jan-10000 -----------------------------------| girijesh | 111 | feb-20000 -----------------------------------| girijesh | 111 | mar-30000 -----------------------------------| ajay | 121 | jan-10000 -----------------------------------| ajay | 121 feb-20000 -----------------------------------| this wat if i executed ur query i got but i want reslut like below see ur ouput and wat am accepted output so now result like below -----------------------------------------------------| empname | reference | salary -----------------------------------------------------| girijesh | 111 | jan-10000,feb-20000,mar-30000 -----------------------------------------------------| ajay | 121 | jan-10000,feb-20000 -----------------------------------------------------| i want query to display like above result
Use sort by or group by with the query to get the result.