quote:

---

Does anybody have any examples of CrossTab Queries without using Cursors or Temp Tables or a stored proc?

---

quote:

---

Originally posted by khtan

quote:

---

Does anybody have any examples of CrossTab Queries without using Cursors or Temp Tables or a stored proc?

---

How about doing it in your front end reporting tool ?

---

KH

---

quote:

---

Originally posted by jsmith8858

Why do you need to do it in T-SQL ? Transforming data into that format isn't something that relational databases are really good at. You'll end up needing much more code and it will be much less efficient than doing it at your presentation later.

How and where are you outputting your results? A web page, a report, windows app, something else? It is usually very trivial to format/arrange your output there, since that's where it should be done, not at the database layer.

- Jeff
http://weblogs.sqlteam.com/JeffS

---

quote:

---

Originally posted by jsmith8858

Guess what, I am not a novice programmer either. If you ask for advice, sometimes it is worthwhile to actually listen to it and consider it, right ? Otherwise, why bother?

Why do you refuse to explain to us why you feel it MUST be done in T-SQL? If you can explain that, I'll show you how to do this in T-SQL ... it will be ugly, long and inefficient, but if you must do it in T-SQL, it can be done. my entire point is that it is very rare -- if ever -- that you NEED to do this in T-SQL.

How are you outputting these results ????



- Jeff
http://weblogs.sqlteam.com/JeffS

---

select	EmployeeID,
	Max(Case when KinNumber = 1 then NextofKinName else null end) as Name1,
	Max(Case when KinNumber = 2 then NextofKinName else null end) as Name2,
	Max(Case when KinNumber = 3 then NextofKinName else null end) as Name3,
	Max(Case when KinNumber = 4 then NextofKinName else null end) as Name4
from	(select	EmployeeID,
		NextofKinName,
		count(*) as KinNumber
	from	[YourTable]
		inner join [YourTable] Ordinal
			on [YourTable].EmployeeID = Ordinal.EmployeeID
			and [YourTable].PersonID >= Ordinal.PersonID
	group by EmployeeID,
		NextOfKinName) Subquery
group by EmployeeID