PIVOT

Question

Hello,

I've searched around and can't quite seem to find a solution that fits mine, so I'll ask the question.

I'm sure you guys have answered this one millions of times before and are sick of seeing topics on Pivot Tables, So I'll make this as clear and brief as possible for your ease.

I have a table containing Name, Address, Postcode and a column with a flag for Director (YES or NO). 

Frequently we get requests to break the data down vs two variables, Post Area and the flag involved. So I'd written a function to get the post area part of a postcode, then I just write a quick select statement grouped by for instance Post Area and Director and get something like this:

POSTAREA DIRECTOR RECORDS
PO1 NO 41
PO1 YES 6
PO10 NO 51
PO10 YES 12
PO11 NO 61
PO11 YES 18
PO12 NO 69
PO12 YES 17
PO13 NO 76
PO13 YES 10
PO14 NO 58

Obviously the records column is just a COUNT(*) of all the records in the table. What I want to know is if there is a way to pivot this and group by POSTAREA column and have YES and NO at the top, I don't want to have to type out the pivoted values so if there a way to create these dynamically from a column? 

Thanks in advance.

Answer 1

Sorry,untested

DECLARE @pivot_cols NVARCHAR(1000);
SELECT @pivot_cols =
        STUFF((SELECT DISTINCT '],[' + DIRECTOR 
               FROM tbl
               ORDER BY '],[' + DIRECTOR 
               FOR XML PATH('')
               ), 1, 2, '') + ']';

DECLARE @pivot_query NVARCHAR(2000);
SET @pivot_query =
N'SELECT ' + @pivot_cols +
  'FROM TBL' +
  'PIVOT ' +
  '(MAX(RECORDS) FOR DIRECTOR  IN (' + @pivot_cols + ')) AS P;';

EXEC(@pivot_query);

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

hi

You can use the mechanism of dynamic pivot :

to see details with example click on below link

http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 3

Thanks to both of you! I actually used both your answers to stumble upon a solution to my problem. Here's my code, which is somewhat similar:

@TABLENAME NVARCHAR(500),
@COLUMN NVARCHAR(500)

AS

DECLARE @SQLEXEC NVARCHAR(4000)
DECLARE @STRING NVARCHAR(500)
DECLARE @TOTAL NVARCHAR(500)

CREATE TABLE #TEMP (COLUMNVALUES NVARCHAR(500))

--GET DISTINCT VALUES FOR COLUMN NAMES--

SET @SQLEXEC='
INSERT INTO #TEMP
SELECT DISTINCT '+@COLUMN+'
FROM '+@TABLENAME+''
EXEC (@SQLEXEC)

--CONSTRUCT THE SELECT LIST VARIABLES--

SELECT @STRING = COALESCE(+@STRING+', ','') + '[' + COLUMNVALUES + ']' FROM #TEMP
SELECT @TOTAL = REPLACE(@STRING,',','+')

--PIVOT THE DATA--

SET @SQLEXEC='
SELECT POSTDISTRICT, '+@STRING+', '+@TOTAL+' AS TOTAL
FROM (SELECT POSTDISTRICT, '+@COLUMN+', RECORDS
FROM '+@TABLENAME+') AS C
PIVOT
(
SUM(RECORDS)
FOR '+@COLUMN+' IN ('+@STRING+')
) AS P'
EXEC (@SQLEXEC)