I'm querying a table which holds values in the following format:
Name Amount
-----------------
Dside 75
Dside 125
Ex 75
Ex 125
Fin 75
Fin 125
Fin 80
I need to query the table and return the results in the following format:
Name Amount1 Amount2 Amount3
------------------------------
Dside 75 125
Ex 75 125
Fin 55 130 80
The amount could go on for 5 or 6 columns - it depends on what data is captured.
Can anybody help?
Here I have created some sample query to get the appropriate result. I integrate the EricZ solution. This query don't have limit for amount you can have n number of amount for Name.
Declare @MaxColumnCount int
--Get max column count
select @MaxColumnCount = Max(CountName) from (
Select COUNT(Name) as CountName from [dbo].[SamplePivot] group by Name
)MaxCount
--print @MaxColumnCount
--Create columnname.
Declare @column_list nvarchar(max)
Declare @id_list nvarchar(max)
Declare @table table(id int,ColumnName nvarchar(max))
Declare @increment int = 1
while(@increment!=@MaxColumnCount)
begin
insert into @table values(@increment, 'Amount'+CAST(@increment as varchar))
set @increment = @increment +1
end
insert into @table values(@increment, 'Amount'+CAST(@increment as varchar))
--select * from @table
--Now combine all column name
select @column_list = COALESCE(@column_list + ',['+cast(id as varchar)+'] as [' + ColumnName + ']', '['+cast(id as varchar)+'] as [' + ColumnName + ']')
, @id_list = COALESCE(@id_list + ',['+cast(id as varchar)+']', '['+cast(id as varchar)+']')
from @table
print @column_list
print @id_list
--Use pivot dynamic query
Declare @sql nvarchar(max)
Set @sql = 'WITH cte AS (
SELECT [Name], [Amount],
ID = ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY Amount)
FROM [dbo].[SamplePivot]
)
SELECT Name, '+@column_list+'
FROM (SELECT [Name] ,[Amount] ,[ID] FROM cte) AS SourceTable
PIVOT (MAX(Amount) FOR [ID] IN ('+@id_list+')) as pvt '
Print @sql
EXEC sp_executesql @sql
SQL Fidde DEMO
Here is an example to display up to 6 [Amount] as clouumns. If you have more than 6 Amount per [Name] or it's dynamic, you may look into this answer T-SQL dynamic pivot
WITH cte AS (
SELECT [Name], [Amount],
ID = ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY Amount)
FROM Table1
)
SELECT Name, [1] AS Amount1, [2] AS Amount2, [3] AS Amount3, [4] AS Amount4,[5] AS Amount5, [6] AS Amount6
FROM (SELECT [Name] ,[Amount] ,[ID] FROM cte) AS SourceTable
PIVOT (MAX(Amount) FOR [ID] IN ([1], [2], [3], [4], [5], [6])) as pvt
SQL Fidde DEMO
