Make one row of three columns from multiple rows

1

Hi There, I have three lines per client and I want to make one line with three columns. I can't think of a way to do this in MSSQL. Please help.

Select 
	Replace(Replace(xc.C_ClientName,'_INC',''),'_BA','') [Book Name]
	, Case When xc.C_ClientName Not Like '%[_]INC' And xc.C_ClientName Not Like '%[_]BA' Then Count(xm.ID) Else 0 End [BCC Matters]
	, Case When xc.C_ClientName Like '%[_]INC' Then Count(xm.ID) Else 0 End [LCC Matters]
	, Case When xc.C_ClientName Like '%[_]BA' Then Count(xm.ID) Else 0 End [BA Matters]
From Client xc with (nolock)
	Inner Join Matter xm with (nolock) on xm.M_ClientID = xc.ID
	Inner Join MatterFileStatus xmfs with (nolock) on xmfs.IDX = xm.M_IDX
	Inner Join Department xd with (nolock) on xd.ID = xmfs.M_DepartmentID
Group By xc.C_ClientName
Order by xc.C_ClientName
2

How to post a T-SQL question on a public forum | spaghettidba

3

Thanks for the information.

4

I will try again, sorry.

This is what I get
Prefix Client BCC Matters LCC Matters BA Matters
ACC4 Accelerated Education Enterprises (Pty) Ltd 3 0 0
ACC4 Accelerated Education Enterprises (Pty) Ltd_Inc 0 1 0
ATH10 Atholton Primary School 2 0 0
ATH10 Atholton Primary School_BA 0 0 19
ATH10 Atholton Primary School_Inc 0 1 0
ATT2 Dr Attwood - Smith_BA 0 0 1
BER18 Berea West Pre Primary School 3 0 0
BER18 Berea West Pre Primary School _BA 0 0 4
BER18 Berea West Pre Primary School _Inc 0 1 0
This is what I am looking for
Prefix Client BCC Matters LCC Matters BA Matters
ACC4 Accelerated Education Enterprises (Pty) Ltd 3 1 0
ATH10 Atholton Primary School 2 1 19
ATT2 Dr Attwood - Smith_BA 0 0 1
BER18 Berea West Pre Primary School 3 1 4

This is the data and scripts:

Use [tempdb];
Go

IF OBJECT_ID('tempdb..#TempFile') IS NOT NULL DROP TABLE #TempFile;
Go

CREATE TABLE [dbo].[#TempFile](
[Prefix] nvarchar NOT NULL,
[Client] nvarchar NOT NULL,
[ID] nvarchar NOT NULL
);
Go

INSERT INTO #TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd_Inc','ACC4-0072');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School ','BER18-0033');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0021');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0102');
INSERT INTO #TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd','ACC4-0081');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0146');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0149');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0170');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0171');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0143');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0065');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0077');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0153');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0156');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0169');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0175');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School','ATH10-0167');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0029');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_Inc','ATH10-0154');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0123');
INSERT INTO #TempFile VALUES ('ATT2','Dr Attwood - Smith_BA','ATT2-0034');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0165');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0025');
INSERT INTO #TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd','ACC4-0089');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0145');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0147');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School','ATH10-0174');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0126');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0168');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School _Inc','BER18-0031');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School ','BER18-0032');
INSERT INTO #TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0150');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0022');
INSERT INTO #TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd','ACC4-0098');
INSERT INTO #TempFile VALUES ('BER18','Berea West Pre Primary School ','BER18-0034');
Go

Select Prefix As [Prefix]
, Client As [Client]
, Case When Client Not Like '%[]INC' And Client Not Like '%[]BA' Then Count(ID) Else 0 End [BCC Matters]
, Case When Client Like '%[]INC' Then Count(ID) Else 0 End [LCC Matters]
, Case When Client Like '%[]BA' Then Count(ID) Else 0 End [BA Matters]
From #TempFile
Group By Prefix, Client
Order By Prefix;

IF OBJECT_ID('tempdb..#TempFile') IS NOT NULL DROP TABLE #TempFile;

Please help and thanks for the advice.

5

Change the GROUP BY and the ORDER BY:


GROUP BY Replace(Replace(xc.C_ClientName,'_INC',''),'_BA','')
ORDER BY Replace(Replace(xc.C_ClientName,'_INC',''),'_BA','')
6

To save yourself a lot of pain, always define the length of strings. eg nvarchar should be something like nvarchar(100).

SELECT T.Prefix, X.Client
	,SUM(CASE WHEN X.CType = 'BCC' THEN 1 ELSE 0 END) AS BCCMatters
	,SUM(CASE WHEN X.CType = 'LCC' THEN 1 ELSE 0 END) AS LCCMatters
	,SUM(CASE WHEN X.CType = 'BA' THEN 1 ELSE 0 END) AS BAMatters
FROM #TempFile T
	CROSS APPLY(VALUES(REVERSE(T.Client))) R (Client)
	CROSS APPLY
	(
		VALUES
		(
			CASE
				WHEN R.Client LIKE 'AB_%'
				THEN REVERSE(SUBSTRING(R.Client, 4, 255))
				WHEN R.Client LIKE 'cnI_%'
				THEN REVERSE(SUBSTRING(R.Client, 5, 255))
				ELSE T.Client
			END
			,CASE
				WHEN R.Client LIKE 'AB_%'
				THEN 'BA'
				WHEN R.Client LIKE 'cnI_%'
				THEN 'LCC'
				ELSE 'BCC'
			END
		)
	) X (Client, CType)
GROUP BY T.Prefix, X.Client;
7

Hi Scott, thanks for the help but this did not work because I need to include the "bap.MatterPrefix, xc.C_ClientName" in the Group by Clause

8

Then we need to add the Prefix to the query, and change the aggregate function a bit:


Select Prefix As [Prefix]
, Replace(Replace(Client,'_INC',''),'_BA','') As [Client]
, SUM(Case When Client Not Like '%[_]INC' And Client Not Like '%[]BA' Then 1 Else 0 End) [BCC Matters]
, SUM(Case When Client Like '%[_]INC' Then 1 Else 0 End) [LCC Matters]
, SUM(Case When Client Like '%[_]BA' Then 1 Else 0 End) [BA Matters]
From #TempFile
GROUP BY Prefix, Replace(Replace(Client,'_INC',''),'_BA','')
ORDER BY Prefix, Replace(Replace(Client,'_INC',''),'_BA','')
9

Hi Ifor, thanks for the help. This works but I have no idea why or how. This blew my mind.

10

The logical order of processing here is:
FROM
APPLY - in order
GROUP BY
SELECT

FROM #TempFile - columns from #TempFile
CROSS APPLY ... R - Adds another column Client which is the reverse of the Client column in #TempFile
CROSS APPLY ... X - First strips _BA and _Inc from the end of Client. Then creates a code based on the end of the original Client.
The GROUP BY and SELECT are then a standard cross tab.

11

Hi Scott, thanks for the help again. This now works perfectly. I also understand your version better than Ifor's version. I am working through Ifor's solution slowly to try and understand it. Thanks again.

12

Hi Ifor, I am working through this, very slowly. Thanks for the help.

13

hi Eugene

hope this helps

create data script

declare @TempFile table ([Prefix] nvarchar (100) NOT NULL,[Client] nvarchar(100) NOT NULL,[ID] nvarchar(100) NOT NULL);

INSERT INTO @TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd_Inc','ACC4-0072');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School ','BER18-0033');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0021');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0102');
INSERT INTO @TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd','ACC4-0081');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0146');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0149');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0170');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0171');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0143');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0065');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0077');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0153');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0156');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0169');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0175');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School','ATH10-0167');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0029');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_Inc','ATH10-0154');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0123');
INSERT INTO @TempFile VALUES ('ATT2','Dr Attwood - Smith_BA','ATT2-0034');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0165');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0025');
INSERT INTO @TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd','ACC4-0089');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0145');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0147');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School','ATH10-0174');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0126');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0168');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School _Inc','BER18-0031');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School ','BER18-0032');
INSERT INTO @TempFile VALUES ('ATH10','Atholton Primary School_BA','ATH10-0150');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School _BA','BER18-0022');
INSERT INTO @TempFile VALUES ('ACC4','Accelerated Education Enterprises (Pty) Ltd','ACC4-0098');
INSERT INTO @TempFile VALUES ('BER18','Berea West Pre Primary School ','BER18-0034');

select 
   [Prefix]
 , replace(replace(Client,'_Inc',''),'_BA','')
 , sum(case when [Client] like '%Ltd' then 1 when client like '%School ' then 1 when client like '%School' then 1 else 0 end ) as BCC_Matters
 , sum(case when [Client] like '%_Inc' then 1 else 0 end  ) as LCC_Matters
 , sum(case when [Client] like '%_BA' then 1 else 0 end )  as BA_Matters
from 
   @TempFile 
group by 
    Prefix
  , replace(replace(Client,'_Inc',''),'_BA','')

image
image1228×183 9.01 KB