I have data in tbl1:

```
item date amount
a 1 10
a 2 20
a 3 30
a 4 40
b 1 20
b 2 30
b 3 40
b 4 50
c 1 30
c 2 40
c 3 50
c 4 60
```

But i need like below

```
item 1 2 3 4
a 10 20 30 40
b 20 30 40 50
c 30 40 50 60
```

# Best How To :

You can use `GROUP BY`

with conditional aggregation using `CASE`

like this.

**Sample Data**

```
DECLARE @tbl1 TABLE (item CHAR(1),date int, amount int)
INSERT INTO @tbl1 VALUES
('a', 1, 10),
('a', 2, 20),
('a', 3, 30),
('a', 4, 40),
('b', 1, 20),
('b', 2, 30),
('b', 3, 40),
('b', 4, 50),
('c', 1, 30),
('c', 2, 40),
('c', 3, 50),
('c', 4, 60);
```

**Query**

```
SELECT
item,
MAX(CASE WHEN date = 1 then amount END) as d1,
MAX(CASE WHEN date = 2 then amount END) as d2,
MAX(CASE WHEN date = 3 then amount END) as d3,
MAX(CASE WHEN date = 4 then amount END) as d4
FROM @tbl1
GROUP BY item
```

**Output**

```
item d1 d2 d3 d4
a 10 20 30 40
b 20 30 40 50
c 30 40 50 60
```

**Edit** If you have unknown number of date, then use `dynamic pivot`

like this.

```
DECLARE @s NVARCHAR(MAX)
SELECT @s = STUFF((SELECT DISTINCT ',' + quotename(CONVERT(VARCHAR(10),date),'[')
FROM #tbl1 for xml path('')),1,1,'')
SET @s = N'SELECT item,' + @s + ' FROM #tbl1 PIVOT(MAX(amount) FOR date in(' + @s + ')) as pvt'
print @s
EXEC sp_executeSQL @s
```