• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Pivot help, please

I have the following tables (just a simplified example):
tblCompany(ID_Company, FK_ID_Code) each company has many codes
tblCodes(ID_Code, strCode)
tblEmployee(FK_ID_Company, ID_Employee, strName)
tblEmployeeCode(FK_ID_Company, FK_ID_Employee, FK_ID_Code, dblValue) each employee may have many codes, though he may not have all

What I want to generate is:
Name      strCode      strCode     strCode [...] etc
John       12               0                34         [...] etc... if a value doesn't exist, replace by 0
[...]

Could someone help me with this, please? I could never get the hang of pivots (probably cause I rarely ever use them).
0
Cluskitt
Asked:
Cluskitt
1 Solution
 
JacobfwCommented:
You can use the PIVOT and UNPIVOT for this purpose
http://msdn.microsoft.com/en-us/library/ms177410.aspx

0
 
CluskittAuthor Commented:
I know I can use PIVOT and UNPIVOT for that. I just could never really understand exactly how to use it, especially when the row number can vary depending on the company (one query could return 20 rows, another could return 25, which would mean a different number of columns on each)
0
 
JestersGrindCommented:
It sounds like you want to do a pivot with dynamic columns like described in this article.

http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Although, looking at your example, I don't know if a pivot is going to work.  You have the same column heading for all of pivoted columns.  I think those need to be distinct values.

Greg

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AnujCommented:
Try dynamic SQL

CREATE TABLE #emp
(eName VARCHAR(20),
 strCode INT
 )
 
 
 INSERT INTO #emp
  ( eName, strCode )
SELECT 'A', 10
UNION ALL
SELECT 'A', 20
UNION ALL
SELECT 'A', 30
UNION ALL
SELECT 'A', 40
UNION ALL
SELECT 'A', 60
UNION ALL
SELECT 'A', 80
UNION ALL
SELECT 'B', 30
UNION ALL
SELECT 'B', 20
UNION ALL
SELECT 'B', 50
UNION ALL
SELECT 'B', 70
UNION ALL
SELECT 'B', 60
UNION ALL
SELECT 'B', 80

SELECT * FROM #emp


declare @Col VARCHAR(max),
            @SQL VARCHAR(MAX)

SET @col = (SELECT STUFF(((SELECT DISTINCT  ','+QUOTENAME('Str'+CONVERT(VARCHAR,strCode)) as 'data()'
                  FROM #emp
                  FOR XML PATH('')
                  )),1,1,''))
                  
            
SET @SQL = 'SELECT  ename, '+@COL
+' FROM    ('
+'          SELECT    ename, strCode, '+'''Str''+' + 'CONVERT(VARCHAR, strCode) tS '
+'          FROM      #emp '
+'         ) AS p PIVOT '
+'( SUM(strCode) FOR ts IN ( '+@COL+' )) AS pvt '
+'ORDER BY ename ;'

EXEC (@SQL)


DROP TABLE #emp
0
 
CluskittAuthor Commented:
Ok, I find both of them a bit confusing. But, while I might get it to work after a few tries, I really don't see how I'm supposed to mix dynamic (codes) with non-dynamic (names) columns in the same query. Using the tables suggested above, could someone generate the intended result? I really don't see how it can be done, but that's just probably due to my lower level of SQL knowledge.
0
 
JestersGrindCommented:
This is based on the following test data.

CREATE TABLE tblCodes (ID_Code INTEGER, strCode VARCHAR(50))
CREATE TABLE tblEmployee (FK_ID_Company INTEGER, ID_Employee INTEGER, strName VARCHAR(50))
CREATE TABLE tblEmployeeCode (FK_ID_Company INTEGER, FK_ID_Employee INTEGER, FK_ID_Code INTEGER, dblValue INTEGER)

INSERT INTO tblCodes VALUES(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D')
INSERT INTO tblEmployee VALUES(1, 1, 'Larry'), (1, 2, 'Moe'), (1, 3, 'Curly')
INSERT INTO tblEmployeeCode VALUES(1, 1, 1, 12), (1, 1, 2, 34), (1, 2, 2, 45), (1, 2, 3, 56), (1, 3, 1, 67), (1, 3, 3, 78), (1, 3, 4, 89)

The following code will dynamically build a pivot statement that has names down the left side, code names across the top and converts null values to 0.

Enjoy!

Greg




DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX)

SET @Columns = (SELECT STUFF(((SELECT DISTINCT  ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCode)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCode))
FROM   
	tblEmployee a INNER JOIN
	tblEmployeeCode b ON a.FK_ID_Company = b.FK_ID_Company AND a.ID_Employee = b.FK_ID_Employee INNER JOIN
	tblCodes c ON b.FK_ID_Code = c.ID_Code
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT DISTINCT  ', '+QUOTENAME(CONVERT(VARCHAR,c.strCode))
FROM   
	tblEmployee a INNER JOIN
	tblEmployeeCode b ON a.FK_ID_Company = b.FK_ID_Company AND a.ID_Employee = b.FK_ID_Employee INNER JOIN
	tblCodes c ON b.FK_ID_Code = c.ID_Code
                  FOR XML PATH('')
                  )),1,1,''))

SET @Query = 'SELECT  strName, ' + @Columns + ' 
FROM 
(SELECT    a.strName, c.strCode, ISNULL(CONVERT(VARCHAR, b.dblValue), '''') dblValue 
FROM tblEmployee a INNER JOIN
	tblEmployeeCode b ON a.FK_ID_Company = b.FK_ID_Company AND a.ID_Employee = b.FK_ID_Employee INNER JOIN
	tblCodes c ON b.FK_ID_Code = c.ID_Code) AS p 
PIVOT 
(MAX(dblValue) FOR strCode IN (' + @Columnnames + ')) AS v '
+'ORDER BY strName ;'

EXECUTE sp_ExecuteSQL @Query

Open in new window

0
 
CluskittAuthor Commented:
I am getting an error (Msg 102, Level 15, State 1, Line 4. Incorrect syntax near 'uv_Cadastro'.). This is my code after adapting to the real tables/views. Pretty much the same, it's just the names that change (uv_Cadastro is a view):
DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX)

SET @Columns = (SELECT STUFF(((SELECT DISTINCT  ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT DISTINCT  ', '+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento
                  FOR XML PATH('')
                  )),1,1,''))

SET @Query = 'SELECT  psa_nome_abreviado, ' + @Columns + ' 
FROM 
(SELECT    a.psa_nome_abreviado, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '''') dblValorVencimento 
uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento) AS p 
PIVOT 
(MAX(dblValorVencimento) FOR strCodigoVenc IN (' + @Columnnames + ')) AS v '
+'ORDER BY psa_nome_abreviado ;'

EXECUTE sp_ExecuteSQL @Query

Open in new window

0
 
CluskittAuthor Commented:
Might as well tell you the fieldtypes (which I forgot):
psa_emp_empresa, FK_ID_intEmpresa SMALLINT
psa_num_empregado, FK_ID_intFuncionario INT
strCodigoVenc VARCHAR(50)
FK_ID_intVencimento SMALLINT
psa_nome_abreviado VARCHAR(200)
dblValorVencimento NUMERIC(12,2)
0
 
JestersGrindCommented:
In the dynamic SQL part you are missing FROM before the view.  It should look like this.  The missing part is bold.

SET @Query = 'SELECT  psa_nome_abreviado, ' + @Columns + '
FROM
(SELECT    a.psa_nome_abreviado, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '''') dblValorVencimento
FROM uv_Cadastro a INNER JOIN
        tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario INNER JOIN
        tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento) AS p
PIVOT
(MAX(dblValorVencimento) FOR strCodigoVenc IN (' + @Columnnames + ')) AS v '
+'ORDER BY psa_nome_abreviado ;'

Greg
0
 
CluskittAuthor Commented:
Excellent. For some reason I had deleted that FROM. I was only looking for it in the top query (silly SQL Studio and it's faulty error pointing! ;)
0
 
CluskittAuthor Commented:
Using a slight variation (because of a couple variables) I get an error. Could you maybe see why this would be?
DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX), @Emp smallint, @AnoMes numeric(6,0)

SET @Emp = 41
SET @AnoMes = 201110

SET @Columns = (SELECT STUFF(((SELECT DISTINCT  ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento
WHERE psa_emp_empresa=@Emp
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT DISTINCT  ', '+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento
WHERE psa_emp_empresa=@Emp
                  FOR XML PATH('')
                  )),1,1,''))

SET @Query = 'SELECT  psa_nome_abreviado, ' + @Columns + ' 
FROM 
(SELECT    a.psa_nome_abreviado, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '''') dblValorVencimento 
FROM uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=' + @AnoMes + ' INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento
WHERE psa_emp_empresa=@Emp) AS p 
PIVOT 
(MAX(dblValorVencimento) FOR strCodigoVenc IN (' + @Columnnames + ')) AS v '
+'ORDER BY psa_nome_abreviado ;'

EXECUTE sp_ExecuteSQL @Query

Open in new window

0
 
CluskittAuthor Commented:
The error is: Msg 8115, Level 16, State 6, Line 25
Arithmetic overflow error converting nvarchar to data type numeric.
0
 
CluskittAuthor Commented:
Nevermind, figured it out. Had to convert the variables to char first :P
0
 
BartVxCommented:
Hi Cluskitt,

I think the problem is the following:

Converting to VARCHAR without length specified converts to VARCHAR(50) by default, try specifying a larger length in your converts:

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now