CODEWITHSUNDEEP
sqlsql-serverpivot-table
HallowMan07
HallowMan07

Reputation: 9

SQL Server create title field from records

Name   StockName     Amount
---------------------------
John   Product 1     40
John   Product 2     20
Mary   Product 1     10
Mary   Product 2     50
Mary   Product 3     9

My current records are shown above. What I want is to be listed as follows:

Name    Product 1 Product 2   Product 3
---------------------------------------
John    40         20          0
Mary    10         50          9

Can it be done with a T-SQL query?

Upvotes: 0

Views: 149

Answers (5)

Atmira
Atmira

Reputation: 249

I would probably keep my dynamic variables in a seperate declaration like this but same logic as John's answer:

DECLARE @SQL VARCHAR(1000)
DECLARE @Cols VARCHAR(1000)

SET @Cols = Stuff((
            SELECT DISTINCT ',' + ' ' + QuoteName(StockName) + ''
            FROM [TableName] A
            ORDER BY 1
            FOR XML Path('')
            ), 1, 1, '') + ''
SET @SQL = ' 

Select
        Name
        ,' + @Cols + '
     From  [TableName] A
      Pivot (sum([Amount]) For [StockName] in ( ' + @Cols + ')
      ) as pvt
      '

    --  Print @SQL
    EXEC (@SQL)

Giving this output:

Name    Product 1   Product 2   Product 3
John    40          20          NULL
Mary    10          50          9

Upvotes: 0

HallowMan07
HallowMan07

Reputation: 9

I need to improve myself a little bit in POVIT. The number of products will increase as new records are added. Then product 5, product 6 etc. can be added.

PIVOT
(
SUM(Amount)
FOR StockName in (

**Stock Name List Query**

)
) as pvt

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82010

Just because I didn't see a Dynamic Pivot

Example

Declare @SQL varchar(max) = '
Select *
 From  YourTable A
 Pivot (sum([Amount]) For [StockName] in (' + Stuff((Select Distinct ','+QuoteName(StockName) 
                                               From  YourTable A  
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'
--Print @SQL
Exec(@SQL)

Returns

Name    Product 1   Product 2   Product 3
John    40          20          NULL
Mary    10          50          9

Upvotes: 2

Atmira
Atmira

Reputation: 249

You need to make a pivot-table. The coding would be like this. Remember to replace [TableName]:

SELECT *
FROM [TableName]
PIVOT(SUM(Amount) FOR StockName IN (
            [Product 1]
            ,[Product 2]
            ,[Product 3]
            )) AS pvt

This will give you this output:

Name    Product 1   Product 2   Product 3
John    40          20          NULL
Mary    10          50          9

If you need 0 values instead of NULL you can do this instead:

SELECT 
    [Name]
    ,ISNULL([Product 1],0) AS [Product 1]
    ,ISNULL([Product 2],0) AS [Product 2]
    ,ISNULL([Product 3],0) AS [Product 3]
FROM [TableName]

PIVOT
(
SUM(Amount)
FOR StockName in ([Product 1], [Product 2], [Product 3])
) as pvt

Output like:

Name    Product 1   Product 2   Product 3
John    40          20          0
Mary    10          50          9

Upvotes: 1

GP Van Eron
GP Van Eron

Reputation: 66

For a table with a schema defined as above

CREATE TABLE tableA(
    Name nvarchar(10),
    StockName nvarchar(20),
    amount int
)

Something similar to the following should do the trick

SELECT 
    Name,
    ISNULL([Product 1],0) AS 'Product 1',
    ISNULL([Product 2],0) AS 'Product 2',
    ISNULL([Product 3],0) AS 'Product 3'
FROM   
(
    SELECT 
        Name, 
        StockName, 
        Amount  
    FROM 
        tableA) p  
    PIVOT  
    (  
        SUM ( Amount  )  
        FOR StockName IN  
            ([Product 1], [Product 2], [Product 3])  
    ) AS pvt

Upvotes: 1

Related Questions