1

Lets say i have a table Sales

With this select i get this.

SELECT CustID, Date, ArtikelNr FROM Sales

CustID  Date         ArtikelNr
1       23.02.2016   50
1       14.02.2016   40
2       22.02.2016   40
1       24.02.2016   50

Now i want the Output like this.

CustID  ArtikelNr Date1        Date2       Date3  ... DateN
1       50        23.02.2016   24.02.2016
1       40        14.02.2016
2       50        24.02.2016

How can i do this?

user2210516
  • 613
  • 3
  • 15
  • 32
  • So you want a dynamic number of columns, depending on the table's different date values? – jarlh Feb 23 '16 at 09:06
  • 3
    Can you add some more rows to the sample data and result – Pரதீப் Feb 23 '16 at 09:09
  • 1
    Have a look at this [link](http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/). It might help you come up with a query. Sadly SQLFiddle for SQL server is not working so I can't try it. – Utsav Feb 23 '16 at 09:16
  • @jarlh Yes that's exactly what i mean. – user2210516 Feb 23 '16 at 09:22
  • 1
    @MotoGP This is not how my table Looks like i just wanted to Show in simple how i mean – user2210516 Feb 23 '16 at 09:23
  • check [this](http://www.codeproject.com/Tips/1011591/Dynamic-Pivot-Query-in-SQL-Server) – Rajen Raiyarela Feb 23 '16 at 10:16
  • You will have to create a procedure for this. I don't think you can accomplish this with just a query. You may need to construct a temporary table with the data you want to accomplish this. – Vivek Verma Feb 23 '16 at 10:16
  • Look at the [answer](http://stackoverflow.com/a/35350343/243373) I gave to [this question](http://stackoverflow.com/q/35347342/243373) as an example. It's similar except you would be partitioning `CustId` and `ArtikelNr`, and ordering by `[Date]`. – TT. Feb 23 '16 at 10:28

1 Answers1

4

I think there's perhaps a problem with either your input data, or example output, since your last output row doesn't seem to match any of your inputs.

I think you're asking for a dynamic pivot, something like this:

Test data

SET DATEFORMAT DMY
CREATE TABLE #Test (CustId INT, Date DATE, ArtikelNr INT)
INSERT INTO #Test VALUES
(1,'23.02.2016',50),
(1,'14.02.2016',40),
(2,'22.02.2016',40),
(1,'24.02.2016',50)

Query

DECLARE @Query AS NVARCHAR(MAX)
DECLARE @Cols AS NVARCHAR(MAX)
SELECT @Cols= ISNULL(@Cols + ',','') + QUOTENAME(RN) FROM (SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY CustId, ArtikelNr ORDER BY CustId) RN FROM #Test) Z

SET @Query = 
  N'SELECT * FROM 
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY CustId, ArtikelNr ORDER BY CustId) RN FROM #Test) A
    PIVOT ( MIN(DATE) FOR RN IN ('+@Cols+')) PVT
ORDER BY CustId, ArtikelNr DESC'

EXEC sp_executesql @Query

CustId      ArtikelNr   1          2
----------- ----------- ---------- ----------
1           50          2016-02-23 2016-02-24
1           40          2016-02-14 NULL
2           40          2016-02-22 NULL
Liesel
  • 2,929
  • 2
  • 12
  • 18