CODEWITHSUNDEEP
sql-serverpivot
JTR
JTR

Reputation: 333

The Column was specified multiple times when using dynamic pivot query?

So let's say that I have two tables, table1 and table2, that I want to pivot with a cte view uvw_TestView.

table1

id1 | name | locationCode
----|------|-----------------
1   | a    | 3
2   | b    | 1
3   | c    | 2

table2

id2 | id1 | StudyName | dateStudy
----|-----|-----------|------
1   | 1   | Math      | 2015-05-23
2   | 1   | Chemistry | 2015-06-20
3   | 2   | Math      | 2016-09-02
4   | 3   | Physics   | 2016-12-26
5   | 2   | Chemistry | 2017-01-05
6   | 2   | Math      | 2017-06-06
7   | 3   | Chemistry | 2018-02-22

The view here is just a loation where people from table1 study.

the expected result is something like this if the date filtered from 2015-05-01 to 2017-01-01

id1 | name | Math       | Chemistry  | Physics 
1   | a    | 2015-05-23 | 2015-06-20 |   ---
2   | b    | 2016-09-02 |   ---      |   ---
3   | c    |   ---      |   ---      |   2016-12-26

I'm so sorry that I can't show the real query here, but I try to make it similar with it.

I've tried to used this query without the date filter

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @dtDate1 DATETIME, @dtDate2 DATETIME;
SET @dtDate1 = '2015-05-01'
SET @dtDate2 = '2017-01-01'
SET @columns = N'';
SELECT @columns += N', ' + QUOTENAME(StudyName)
  FROM (SELECT DISTINCT t2.StudyName FROM dbo.table2 AS t2) AS x;

SET @sql = N'
WITH cte AS
(
    SELECT * FROM dbo.uvw_TestView WHERE idView = '001'
    UNION ALL
    SELECT a.* FROM dbo.uvw_TestView a INNER JOIN cte b ON a.ParentID = b.idView
SELECT * FROM
    (SELECT DISTINCT * FROM cte) ct
INNER JOIN (
    SELECT p.*, ' + STUFF(@columns, 1, 2, '') + '
    FROM
    (
        SELECT DISTINCT
            tb1.id1,
            tb1.name,
            tb2.dateStudy,
            tb2.StudyName
        FROM dbo.table1 AS tb1
        INNER JOIN dbo.table2 tb2 tb2
        ON tb1.id1 = tb2.id1
    ) AS j
    PIVOT
    (
      MIN(dateStudy) FOR StudyName IN ('
      + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
      + ')
    ) AS p) tbl ON tbl.id1 = ct.id1';
PRINT @sql;
EXEC sp_executesql @sql;

But get the error The column 'Math' was specified multiple times for 'tbl'.

I also tried with the date filter

SELECT @columns += N', ' + QUOTENAME(StudyName)
  FROM (SELECT DISTINCT t2.StudyName FROM dbo.table2 AS t2) AS x;

SET @sql = N'
WITH cte AS
(
    SELECT * FROM dbo.uvw_TestView WHERE idView = '001'
    UNION ALL
    SELECT a.* FROM dbo.uvw_TestView a INNER JOIN cte b ON a.ParentID = b.idView
SELECT * FROM
    (SELECT DISTINCT * FROM cte) ct
INNER JOIN (
    SELECT p.*, ' + STUFF(@columns, 1, 2, '') + '
    FROM
    (
        SELECT DISTINCT
            tb1.id1,
            tb1.name,
            tb2.dateStudy,
            tb2.StudyName
        FROM dbo.table1 AS tb1
        INNER JOIN dbo.table2 tb2 tb2
        ON tb1.id1 = tb2.id1
    ) AS j
    PIVOT
    (
      MIN(dateStudy) FOR StudyName IN ('
      + STUFF(REPLACE(@columns, ', [', ',['), 1, 1, '')
      + ')
    ) AS p) tbl ON tbl.id1 = ct.id1 where tbl.dateStudy BETWEEN ''' + @dtDate1 +''' AND ''' + @dtDate2;

and get the error String or binary data would be truncated.

So, how I resolve this error to get the result?

Upvotes: 0

Views: 1483

Answers (1)

Squirrel
Squirrel

Reputation: 24773

i see you have a PRINT @sql; statement. But did you take a look at the query ?

the error The column 'Math' was specified multiple times for 'tbl'. is due to this line

SELECT p.*, ' + STUFF(@columns, 1, 2, '') + '

your columns would contains the list of columns like [Chemistry], [Math] and p.* actually contains all column from the PIVOT result. So those pivot columns are duplicates

You can just do a SELECT * instead and it should be sufficient.

Upvotes: 1

Related Questions