DECLARE @t TABLE(RecId INT NOT NULL PRIMARY KEY, Responsible INT NOT NULL, Carer INT NOT NULL, TYPE VARCHAR(20) NOT NULL)
INSERT @t SELECT 
1,1,3,'Play' UNION ALL SELECT 
2,5,3,'Play' UNION ALL SELECT 
3,5,2,'Play' UNION ALL SELECT 
4,3,1,'Study' UNION ALL SELECT 
5,2,3,'Play' UNION ALL SELECT 
6,3,1,'Study' UNION ALL SELECT 
7,1,2,'Exercise' UNION ALL SELECT 
8,4,2,'Exercise' UNION ALL SELECT 
9,1,3,'Play'

;WITH CTE(Person, Side, TYPE) AS (
	SELECT Person, Side, TYPE FROM (SELECT * FROM @t) a
	UNPIVOT (Person FOR Side IN(Responsible, Carer)) b),
CTE2(Person, SideType) AS (
	SELECT Person, Side FROM CTE
	UNION ALL
	SELECT Person, Type FROM CTE)
SELECT Person, Responsible, Carer, Play, Study, Exercise 
FROM (select Person, SideType from CTE2) a
PIVOT(Count(SideType) FOR SideType IN(Responsible, Carer, Play, Study, Exercise)) b

Declare @t Table (
	RecId int,
	Responsible int,
	Carer int,
	Type varchar(10)
)
Insert Into @t Select 1, 1, 3, 'Play' 
Insert Into @t Select 2, 5, 3, 'Play'
Insert Into @t Select 3, 5, 2, 'Play'
Insert Into @t Select 4, 3, 1, 'Study'
Insert Into @t Select 5, 2, 3, 'Play'
Insert Into @t Select 6, 3, 1, 'Study'
Insert Into @t Select 7, 1, 2, 'Exercise'
Insert Into @t Select 8, 4, 2, 'Exercise' 
Insert Into @t Select 9, 1, 3, 'Play' 



Select 
	A.Person,	
	Responsible = SUM(case when B.Responsible = A.Person then 1 else 0 end),
	Carer = SUM(case when B.Carer = A.Person then 1 else 0 end),
	Play = SUM(case when Type = 'Play' then 1 else 0 end),
	Study = SUM(Case when Type = 'Study' then 1 else 0 end),
	Exercise = SUM(case when Type = 'Exercise' then 1 else 0 end)
From 
	(
	Select Person = Responsible From @t
	Union
	Select Person = Carer From @t
	) A
Inner Join @t B
On A.Person = B.Responsible
Or A.Person = B.Carer
Group By A.Person

quote:

---

Originally posted by robvolk

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

---

quote:

---

Originally posted by robvolk

If the error is "Syntax error near PIVOT", do a find-replace on "pivot" and change it to "_pivot".

---

quote:

---

Originally posted by robvolk

Better to change all of them.

---

exec crosstab 'SELECT A.Person FROM (select Responsible Person from #t union select Carer from #t) A
INNER JOIN #t B ON A.Person=B.Responsible OR A.Person=B.Carer
GROUP BY A.Person', 'Count(A.Person)', 'Type', '#t'

quote:

---

Originally posted by robvolk

I made the code changes I specified and ran the following successfully:

exec crosstab 'SELECT A.Person FROM (select Responsible Person from #t union select Carer from #t) A
INNER JOIN #t B ON A.Person=B.Responsible OR A.Person=B.Carer
GROUP BY A.Person', 'Count(A.Person)', 'Type', '#t'

If you're getting an error, please post it. If you're not getting an error, then you'll have to elaborate on what's not working.

---

ALTER PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@_pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @_pivot + ' AS _pivot INTO ##_pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##_pivot SELECT DISTINCT ' + @_pivot + ' FROM ' + @table + ' WHERE ' 
+ @_pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##_pivot' AND column_name='_pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), _pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @_pivot + ' WHEN ' 
+ @delim + convert(varchar(100), _pivot) + @delim + ' THEN ' ) + ', ' FROM ##_pivot

DROP TABLE ##_pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

create Table #t(
	RecId int,
	Responsible int,
	Carer int,
	Type varchar(10)
)
Insert Into #t Select 1, 1, 3, 'Play' 
Insert Into #t Select 2, 5, 3, 'Play'
Insert Into #t Select 3, 5, 2, 'Play'
Insert Into #t Select 4, 3, 1, 'Study'
Insert Into #t Select 5, 2, 3, 'Play'
Insert Into #t Select 6, 3, 1, 'Study'
Insert Into #t Select 7, 1, 2, 'Exercise'
Insert Into #t Select 8, 4, 2, 'Exercise' 
Insert Into #t Select 9, 1, 3, 'Play'

exec crosstab 'SELECT A.Person FROM (select Responsible Person from myTable union select Carer from myTable) A
INNER JOIN myTable B ON A.Person=B.Responsible OR A.Person=B.Carer
GROUP BY A.Person', 'Count(A.Person)', 'Type', 'myTable'

exec crosstab 'SELECT A.Person FROM (select Responsible Person from myTable union select Carer from myTable) A
INNER JOIN myTable B ON A.Person=B.Responsible OR A.Person=B.Carer
INNER JOIN Types T ON B.Type=T.Type
GROUP BY A.Person', 'Count(A.Person)', 'T.TypeDescription', 'myTable'