Solved

Posted on 2011-10-19
306 Views
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
Question by:Cluskitt

LVL 7

Expert Comment

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

0

LVL 18

Author Comment

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

LVL 21

Expert Comment

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

LVL 15

Expert Comment

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

LVL 18

Author Comment

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

LVL 21

Accepted Solution

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

LVL 18

Author Comment

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

LVL 18

Author Comment

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

LVL 21

Expert Comment

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

LVL 18

Author Closing Comment

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

LVL 18

Author Comment

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

LVL 18

Author Comment

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

LVL 18

Author Comment

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

LVL 3

Expert Comment

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

### Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.