Pivot varchar fields dynamically with or without aggregate in SQL

Question

0.00/5 (No votes)

See more:

I have a table as below

TName Scode
AKAT Allow1
AKAT Allow2
AKAT Allow3
AKAT Allow4
AkCT Allow1
AKCT Allow2
AKCT Allow3
AKST Allow1
AKST Allow2
AKST Allow3
AKST Allow4
AKST Allow5
. .
. .
. .

I want a result as below:

AKAT AKCT AKST . . .
Allow1 Allow1 Allow1 . . .
Allow2 Allow2 Allow2 . . .
Allow3 Allow3 Allow3 . . .
Allow4 Allow4 .
Allow5

What I have tried:

I have tried multiple queries by googling but none have helped.

There can be many Tnames so i cannot have a static query. and the fields are varchar datatype so not sure if aggregate can be used.

I can add a TID column in the table as numeric field but those TID's will be same for particular Tname. if thats helpful in querying.

Posted 16-May-17 14:32pm

akarsha

Updated 17-May-17 6:25am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2017-05-17T06:25:00

Check this:

SQL

CREATE TABLE #tmp (TName VARCHAR(30), Scode VARCHAR(30))

INSERT INTO #tmp(TName, Scode)
VALUES('AKAT', 'Allow1'),
('AKAT', 'Allow2'),
('AKAT', 'Allow3'),
('AKAT', 'Allow4'),
('AkCT', 'Allow1'),
('AKCT', 'Allow2'),
('AKCT', 'Allow3'),
('AKST', 'Allow1'),
('AKST', 'Allow2'),
('AKST', 'Allow3'),
('AKST', 'Allow4'),
('AKST', 'Allow5')

DECLARE @cols NVARCHAR(4000) = STUFF((SELECT DISTINCT '],[' + TName
				FROM #tmp
				ORDER BY '],[' + TName
				FOR XML PATH('')) , 1, 2, '') + ']'

--SELECT @cols

DECLARE @Qry NVARCHAR(MAX) = N'
SELECT RowNo, ' + @cols +
'FROM (
	SELECT ROW_NUMBER() OVER(PARTITION BY TName ORDER BY Scode) As RowNo, *
	FROM #tmp
	) dt
PIVOT (MAX(Scode) FOR TName IN(' + @cols + ')) pvt'

EXEC(@Qry)

DROP TABLE #tmp

Result:

RowNo	AKAT	AkCT	AKST
1		Allow1	Allow1	Allow1
2		Allow2	Allow2	Allow2
3		Allow3	Allow3	Allow3
4		Allow4	NULL	Allow4
5		NULL	NULL	Allow5