Create table Sample(Empid int primary key identity(1,1),EmpName varchar(50),Empsalary  money,EmpDesignation varchar(30));

insert into Sample values('Jhon',8000,'Manager'),
('Smith',6000,'Lead'),
('Samuel',4000,'AccountExecutive'),
('Simson',4000,'AccountSpecialist'),
('Eric',22000,'Director'),
('Jonathan',12000,'SeniorManager')

select  'EmpName','Jhon','Smith','Samuel','Simson','Eric','Jonathan' union all
select 'Salary','8000','6000','4000','4000','22000','12000' union all
select 'Designation','Manager','Lead','AccountExecutive','AccountSpecialist','Director','SeniorManager'

declare	@sql	varchar(max),
	@empid	varchar(max)

select	@empid	= isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid))
from	Sample
order by Empid

select	@sql	= N'
select	col = ''EmpName'', *
from	(
		select	Empid, EmpName
		from	Sample 
	)s
	pivot
	(
		max(EmpName)
		for	Empid in (' + @empid + ')
	) p
union all
select	col = ''salary'', *
from	(
		select	Empid, Empsalary = convert(varchar(10), Empsalary)
		from	Sample 
	)s
	pivot
	(
		max(Empsalary)
		for	Empid in (' + @empid + ')
	) p
union all
select	col = ''Designation'', *
from	(
		select	Empid, EmpDesignation
		from	Sample 
	)s
	pivot
	(
		max(EmpDesignation)
		for	Empid in (' + @empid + ')
	) p
'

exec	(@sql)

select	TOP 10000 @empid = isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid))
from	Sample
order by Empid

quote:

---

Originally posted by sqllover

Thank a lot for your reply. Have quick question. As i said my table has 2k * 1000 rows. But if i use top 10000 it shows the server resource exceed and complex question will make this issue error. How to avoid this error

So i tried with Top 5000 rows and it take around 10 min to produce the result.

Is there any fastest way to achieve this

---

quote:

---

Msg 8623, Level 16, State 1, Line 2
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

---

select	@empid	= isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid)),
        @empid2 = isnull(@empid + ',', '') + convert(varchar(10), Empid)
from	Sample
order by Empid

from	(
		select	Empid, EmpName
		from	Sample 
                where   Empid in (' + @empid2 + ')
	)s

Msg 207, Level 16, State 1, Line 5
Invalid column name '5647'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '5646'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '5645'.
Msg 207, Level 16, State 1, Line 5
Invalid column name '5644'.

select	@empid	= isnull(@empid + ',', '') + quotename(convert(varchar(10), Empid)),
        @empid2 = isnull(@empid2 + ',', '') + convert(varchar(10), Empid)
from	Sample
order by Empid

SELECT *
FROM
(
select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn
FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s
unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u
)m
PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p
ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

quote:

---

Msg 8623, Level 16, State 1, Line 2
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

---

quote:

---

Originally posted by sqllover

thank khtan, the error gone. the query now little bit fast. for 5000 rows, it took 1 minute 3 seconds.

I have one more try on this logic and here is my try

SELECT *
FROM
(
select Val,Cat,ROW_NUMBER() over (partition by Cat ORDER BY Empid ) AS Rn
FROM (SELECT Empid,Empname,CAST(Empsalary as varchar(50)) AS EmpSalary,CAST(Empdesignation as varchar(50)) AS Empdesignation FROM Sample)s
unpivot (Val FOR Cat IN ([EmpName],[EmpSalary],[EmpDesignation]))u
)m
PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4],[5],[6]))p
ORDER BY CASE Cat WHEN 'Empname' THEN 1 WHEN 'salary' THEN 2 ELSE 3 END

But on this logic, it will currently show 6 records only. Is it possible to make this as dynamic query which will try to hit top 10000 records? please help me on this.

---

quote:

---

Originally posted by sqllover

Hi khtan,

If i make top 10000 on your sample, still it throws below error

quote:

---

Msg 8623, Level 16, State 1, Line 2
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

---

Please help me to avoid this

Thanks

---