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

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
1 Solution

Commented:
You can use the PIVOT and UNPIVOT for this purpose
http://msdn.microsoft.com/en-us/library/ms177410.aspx

0

Author 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

Commented:
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

Commented:
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

Author 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

Commented:
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
``````
0

Author 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
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
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
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 '

EXECUTE sp_ExecuteSQL @Query
``````
0

Author Commented:
Might as well tell you the fieldtypes (which I forgot):
psa_emp_empresa, FK_ID_intEmpresa SMALLINT
strCodigoVenc VARCHAR(50)
FK_ID_intVencimento SMALLINT
dblValorVencimento NUMERIC(12,2)
0

Commented:
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
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 '

Greg
0

Author 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

Author 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
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
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
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 '

EXECUTE sp_ExecuteSQL @Query
``````
0

Author Commented:
The error is: Msg 8115, Level 16, State 6, Line 25
Arithmetic overflow error converting nvarchar to data type numeric.
0

Author Commented:
Nevermind, figured it out. Had to convert the variables to char first :P
0

Commented:
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

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