Repro for bug of collation issue in SQL Server 2012 (see: http://dba.stackexchange.com/q/160975/114952). Repro also on localdb v11.

CollationBugRepro.sql

/****** Object:  Database [CollationTest]    Script Date: 14-1-2017 12:40:46 ******/
CREATE DATABASE [CollationTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'CollationTest', FILENAME = N'C:\Users\Sebastiaan\CollationTest.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'CollationTest_log', FILENAME = N'C:\Users\Sebastiaan\CollationTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [CollationTest] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [CollationTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [CollationTest] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [CollationTest] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [CollationTest] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [CollationTest] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [CollationTest] SET ARITHABORT OFF 
GO
ALTER DATABASE [CollationTest] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [CollationTest] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [CollationTest] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [CollationTest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [CollationTest] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [CollationTest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [CollationTest] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [CollationTest] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [CollationTest] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [CollationTest] SET  DISABLE_BROKER 
GO
ALTER DATABASE [CollationTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [CollationTest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [CollationTest] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [CollationTest] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [CollationTest] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [CollationTest] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [CollationTest] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [CollationTest] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [CollationTest] SET  MULTI_USER 
GO
ALTER DATABASE [CollationTest] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [CollationTest] SET DB_CHAINING OFF 
GO
ALTER DATABASE [CollationTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [CollationTest] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
/****** Object:  Table [dbo].[Component]    Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Component](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Component] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Product]    Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ProductComponentConcentration]    Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductComponentConcentration](
	[ProductId] [int] NOT NULL,
	[ComponentId] [int] NOT NULL,
	[Concentration] [float] NOT NULL,
 CONSTRAINT [PK_ProductComponentConcentration] PRIMARY KEY CLUSTERED 
(
	[ProductId] ASC,
	[ComponentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ProductStatement]    Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductStatement](
	[ProductId] [int] NOT NULL,
	[StatementId] [int] NOT NULL,
 CONSTRAINT [PK_ProductStatement] PRIMARY KEY CLUSTERED 
(
	[ProductId] ASC,
	[StatementId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  UserDefinedFunction [dbo].[QueryConcatenationString]    Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sebastiaan Dammann
-- Create date: 2017-01-14
-- Description:	Repro for collation problem
-- =============================================
CREATE FUNCTION [dbo].[QueryConcatenationString] 
(
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
	RETURN NCHAR(13) + NCHAR(10)
END

GO
/****** Object:  Table [dbo].[Statement]    Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Statement](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Statement] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  UserDefinedFunction [dbo].[QueryData]    Script Date: 14-1-2017 12:40:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sebastiaan Dammann
-- Create date: 2017-01-14
-- Description:	Repro for collation conflict
-- =============================================
CREATE FUNCTION [dbo].[QueryData] 
(	
)
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	cteCmtCauses AS (
		SELECT ProductId = p.Id,
			   Name = s.Name
		FROM Product p
		INNER JOIN ProductStatement ps ON ps.ProductId = p.Id
		INNER JOIN Statement s ON s.Id = ps.StatementId

		UNION ALL

		SELECT ProductId = p.Id,
			   Name = c.Name
		FROM Product p
		INNER JOIN ProductComponentConcentration pc ON pc.ProductId = p.Id
		INNER JOIN Component c ON c.Id = pc.ComponentId
	 ),

	 cteCmtCausesConcat AS (
		SELECT ProductId = p.Id,
			   Concated = (
					-- Here the issue happens
					SELECT CONCAT(N'|', cte.Name COLLATE DATABASE_DEFAULT, dbo.QueryConcatenationString())
					FROM cteCmtCauses cte 
					WHERE cte.ProductId = p.Id
					FOR XML PATH(N''), TYPE
			   )
		FROM Product p
	 )

	 SELECT p.Id, p.Name, cteCmtCausesConcat.Concated
	 FROM Product p
	 INNER JOIN cteCmtCausesConcat ON cteCmtCausesConcat.ProductId = p.Id
)

GO
ALTER DATABASE [CollationTest] SET  READ_WRITE 
GO

Please make the following 3 changes to make the script more accurate:

1. Add COLLATE Latin1_General_CI_AS to the CREATE DATABASE statement, at the end of it, right before the GO.
2. Add:

   USE [CollationTest];  
   GO
   

   after the last ALTER DATABASE / GO
3. Change the return type of [dbo].[QueryConcatenationString] to be NCHAR(2).

Thanks :-)