Hi SMART GUYS,
Please help me write a query. Actually I want to convert my rows into columns. Can anyone kindly give me the query to do it? My rows are about employees. There can be any number of employees (in the following table I have 3 employees ABC, EFG, WRI) with any number of records for each employee (there can be 20, 30 records each), but all employees have the same number of records (in the following table each employee has 3 records). My table is:
USERID Name Role Total
100 ABC 1 0
100 ABC 1 0
100 ABC 1 1
101 EFG 1 0
101 EFG 1 2
101 EFG 1 1
102 WRI 2 1
102 WRI 2 0
102 WRI 2 1
I want to convert the above table in the following form:
USERID Name Role Total1 Total2 Total3
100 ABC 1 0 0 1
101 EFG 1 0 2 1
102 WRI 2 1 0 1
Columns [USERID], [Name], [Role] are unique for each row but Column [Total] is dynamic (in above case, 3 [Total] Columns Total1, Total2, Total3) and depends on the number of records against each employee. If there were 30 records for each employee, the Column [Total] will be (Total1, Total2, Total3…Total30), I have mentioned above that all employees will have the same number of records.
I have even used the Pivot Operator in SQL Server 2005 but it always end up in an error about the syntax proceeding the PIVOT word. I just need a query with/without the PIVOT operator.
I look forward to your reply. I will be highly obliged.
Regards...