Sql PIVOT and string concatenation aggregate
In order to get the result, first you should concatenate the values into the comma separated list.
I would use CROSS APPLY and FOR XML PATH:
SELECT distinct e.[Event Name],
e.[Resource Type],
LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName
FROM yourtable e
CROSS APPLY
(
SELECT r.[Resource Name] + ', '
FROM yourtable r
where e.[Event Name] = r.[Event Name]
and e.[Resource Type] = r.[Resource Type]
FOR XML PATH('')
) r (ResourceName)
See SQL Fiddle with Demo. The gives you result:
| EVENT NAME | RESOURCE TYPE | RESOURCENAME |
------------------------------------------------------------------------
| Event 1 | Resource Type 1 | Resource 1, Resource 2 |
| Event 1 | Resource Type 2 | Resource 3, Resource 4 |
| Event 1 | Resource Type 3 | Resource 5, Resource 6, Resource 7 |
| Event 1 | Resource Type 4 | Resource 8 |
| Event 2 | Resource Type 2 | Resource 3 |
| Event 2 | Resource Type 3 | Resource 11, Resource 12, Resource 13 |
| Event 2 | Resource Type 4 | Resource 14 |
| Event 2 | Resource Type 5 | Resource 1, Resource 9, Resource 16 |
Then you will apply your PIVOT to this result:
SELECT [Event Name],
[Resource Type 1], [Resource Type 2],
[Resource Type 3], [Resource Type 4],
[Resource Type 5]
FROM
(
SELECT distinct e.[Event Name],
e.[Resource Type],
LEFT(r.ResourceName , LEN(r.ResourceName)-1) ResourceName
FROM yourtable e
CROSS APPLY
(
SELECT r.[Resource Name] + ', '
FROM yourtable r
where e.[Event Name] = r.[Event Name]
and e.[Resource Type] = r.[Resource Type]
FOR XML PATH('')
) r (ResourceName)
) src
pivot
(
max(ResourceName)
for [Resource Type] in ([Resource Type 1], [Resource Type 2],
[Resource Type 3], [Resource Type 4],
[Resource Type 5])
) piv
See SQL Fiddle with Demo. Your final result will then be:
| EVENT NAME | RESOURCE TYPE 1 | RESOURCE TYPE 2 | RESOURCE TYPE 3 | RESOURCE TYPE 4 | RESOURCE TYPE 5 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Event 1 | Resource 1, Resource 2 | Resource 3, Resource 4 | Resource 5, Resource 6, Resource 7 | Resource 8 | (null) |
| Event 2 | (null) | Resource 3 | Resource 11, Resource 12, Resource 13 | Resource 14 | Resource 1, Resource 9, Resource 16 |
SQL Server pivot with string concatenation
I have concatenated the answer list first per name and subject, then applied pivoting -
declare @temp table (name varchar(100), subject varchar(100), answer varchar(100))
insert into @temp
select 'Pranesh','Physics' ,'Numerical Problems'
union all select 'Pranesh','Physics' ,'Other'
union all select 'Pranesh','Chemistry','Understanding Concepts'
union all select 'Pranesh','Chemistry','Organic chemistry reactions'
union all select 'Pranesh','Maths' ,'Lack of understanding'
union all select 'Pranesh','Maths' ,'Insufficient practice'
union all select 'Pranesh','Maths' ,'Other'
union all select 'Ramesh','Biology' , 'Other'
union all select 'Ramesh','Biology' , 'Science'
;with cte as (select distinct name, subject from @temp)
select * from
(
select
c.name,
c.subject,
answer = stuff((select ',' + answer from @temp t where t.name=c.name and t.subject=c.subject for xml path('')), 1, 1, '')
from cte c
) src
pivot
(
max(answer) for subject in ([Physics], [Chemistry], [Maths], [Biology],[ComputerScience],[CommonUnderstanding])
) piv
SQL aggregate function against concatenated string in pivot table
Cast [Status] and [Location] to a varchar performing the concatenation. I have arbitrarily chosen 3 as the length of the varchar but that may vary depending on what your maximum possible [Status] and [Location] are.
Also, remove the [Status] and [Location] columns from the inner query.
SELECT *
FROM (
SELECT
empId,
left(datename(dd,[Date]),2)as [day_date],
cast([Status] AS varchar(3)) + '|' + cast([Location] as varchar(3)) as val
FROM [dbo].[table]
WHERE [date] BETWEEN '2014-01-01' AND '2014-01-30') as s
PIVOT (MAX(val) FOR [day_date] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
)AS p
order by empID
Amazon Redshift Pivot using Concatenation
You can use conditional aggregation:
SELECT
foo_id,
MAX(CASE WHEN country = 'US' THEN foo_value END) AS US,
MAX(CASE WHEN country = 'IN' THEN foo_value END) AS "IN",
MAX(CASE WHEN country = 'DE' THEN foo_value END) AS DE
FROM mytable
GROUP BY foo_id
How to use GROUP BY to concatenate strings in SQL Server?
No CURSOR, WHILE loop, or User-Defined Function needed.
Just need to be creative with FOR XML and PATH.
[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]
CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
SQL Server error using the CONCAT function in a pivot aggregated function
Try this,
CREATE TABLE #PIVOT (Time DATETIME, AREA INT, BLOBs INT, CARs VARChar (10))
insert into #PIVOT values
('2018-05-07 16:00:00.000', 02, 11 ,'BMW')
,('2018-05-07 16:15:00.000', 02, 2 ,'BMW')
,('2018-05-07 16:15:00.000', 06, 7 ,'KIA')
,('2018-05-07 16:30:00.000', 06, 8 ,'KIA')
,('2018-05-07 16:45:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 02, 9 ,'BMW')
,('2018-05-07 17:00:00.000', 10, 8 ,'FIA')
select
Time, CARs + convert (varchar (10), [02]) [02]
, CARs + convert (varchar (10), [06]) [06], CARs + convert (varchar (10), [10]) [10]
from (
select
*
from #PIVOT
) p
pivot
(
max (BLOBs) for area in ([02],[06], [10])
) t
Time 02 06 10
2018-05-07 16:00:00.000 BMW11 NULL NULL
2018-05-07 16:15:00.000 BMW2 NULL NULL
2018-05-07 16:45:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 BMW9 NULL NULL
2018-05-07 17:00:00.000 NULL NULL FIA8
2018-05-07 16:15:00.000 NULL KIA7 NULL
2018-05-07 16:30:00.000 NULL KIA8 NULL
Let me work for dynamic query.
Is there a way to take this data, pivot it and then combine it into a single string..?
To get the first column you're after:
; -- see sqlblog.org/cte
WITH src AS
(
SELECT num, [module-id], txt = STUFF(
(SELECT CONCAT(' & ', 'p-', [page-no], ' ', txt)
FROM dbo.StuffAndThings WHERE num = s.num
AND [module-id] = s.[module-id]
FOR XML PATH(''),
TYPE).value(N'./text()[1]', N'varchar(max)'), 1, 3, '')
FROM dbo.StuffAndThings AS s
WHERE [page-no] = 1
)
SELECT num,
[module-id-1] = [1],
[module-id-2] = [2],
[module-id-3] = [3]
FROM src PIVOT
(MAX(txt) FOR [module-id] IN ([1],[2],[3])) AS p;
- Example db<>fiddle
How to aggregate and string concatenate at same time in Oracle?
Would this do?
SQL> with test (customer, product, amount) as
2 (select 'a', 'table', 500 from dual union all
3 select 'a', 'table', 300 from dual union all
4 select 'a', 'chair', 100 from dual union all
5 select 'b', 'rug' , 50 from dual union all
6 select 'b', 'chair', 200 from dual
7 )
8 select customer,
9 listagg (product, ', ') within group (order by null) product,
10 sum(sum_amount) amount
11 from (select customer, product, sum(amount) sum_amount
12 from test
13 group by customer, product
14 )
15 group by customer
16 order by customer;
C PRODUCT AMOUNT
- -------------------- ----------
a chair, table 900
b chair, rug 250
SQL>
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
Related Topics
Finding Duplicate Rows in SQL Server
List Columns with Indexes in Postgresql
How to Convert Rows to Columns in Oracle
Return a Value If No Record Is Found
For Nvarchar(Max) I am Only Getting 4000 Characters in Tsql
Constraint for Only One Record Marked as Default
Address Standardization Within a Database
Protecting Against SQL Injection in Python
Linq Version of SQL "In" Statement
Difference Between Byte and Char in Column Datatypes
Why Can't I Seem to Force Oracle 11G to Consume More Cpus for a Single SQL Query
SQL How to Compare Two Tables for Same Data Content
Selecting N Rows in SQL Server
Huge Performance Difference When Using Group by VS Distinct
How to Run Native SQL with Entity Framework