asked on

Help with SQL statement view

I need some help creating a SQL statement that will display the output in different view.

My current table data looks like this:

VendorName  MonthAsDate  MonthYear  Count
Vendor1     1/1/2018     2018-01    120
Vendor2     1/1/2018     2018-01    9
Vendor3     1/1/2018     2018-01    4877
Vendor4     1/1/2018     2018-01    40
Vendor5     1/1/2018     2018-01    23
Vendor1     2/1/2018     2018-02    97
Vendor2     2/1/2018     2018-02    6
Vendor3     2/1/2018     2018-02    4455
Vendor4     2/1/2018     2018-02    42
Vendor5     2/1/2018     2018-02    60
Vendor1     3/1/2018     2018-03    89
Vendor2     3/1/2018     2018-03    12
Vendor3     3/1/2018     2018-03    4517
Vendor4     3/1/2018     2018-03    49
Vendor5     3/1/2018     2018-03    32
Vendor1     4/1/2018     2018-04    82
Vendor2     4/1/2018     2018-04    12
Vendor3     4/1/2018     2018-04    4109
Vendor4     4/1/2018     2018-04    48
Vendor5     4/1/2018     2018-04    130
Vendor1     5/1/2018     2018-05    113
Vendor2     5/1/2018     2018-05    9
Vendor3     5/1/2018     2018-05    4275
Vendor4     5/1/2018     2018-05    57
Vendor5     5/1/2018     2018-05    17

Open in new window

I'd like a view that outputs the data like this:

MonthYear  Vendor1  Vendor2  Vendor3  Vendor4  Vendor5
2018-01	   120      9        4877     40       23  
2018-02	   97       6        4455     42       60
2018-03	   89       12       4517     49       32
2018-04	   82       12       4109     48       130

Open in new window

Can't this be done with an advanced SQL statement?

Thanks.

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Russ Suter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

greddin

ASKER

Thank you for the help guys. I've got it working now.

Sara bhai

DECLARE @PivotQuery AS NVARCHAR(4000)
DECLARE @ColumnName AS NVARCHAR(4000)

SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(VendorName) FROM (SELECT DISTINCT VendorName FROM Vendor) AS p

SET @PivotQuery = N'SELECT MonthYear, ' + @ColumnName + ' FROM
(SELECT
VendorName ,MonthYear, [COUNT]
FROM Vendor ) up
PIVOT(SUM([COUNT]) FOR VendorName IN (' + @ColumnName + ')) AS PivotResult'

EXEC sp_executesql @PivotQuery