Pivot columns

Question

Post reply

Pivot columns

ksrikanth77

SSCrazy

Points: 2232
More actions
August 7, 2013 at 3:46 pm

#275960

I have a table structure as
ID Name
1 A
2 B
3 C
4 B
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P
17 Q
18 R
19 S
20 T
and so on...
Can anyone help me to get the results either for adding Static columsn around 6 to 7 or Dynamic columns which can accomdate the number of records in the table by count 6 as mentioned below.
ID COL1 COL2 COL3 COL4 COL5 COL6
1 A G M S Y AE
2 B H N T Z AF
3 C I O U AA AG
4 D J P V AB AG
5 E K Q W AC AI
6 F L R X AD AJ
Thanks in Advance
Srikanth Reddy

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 1

Adding the Table create and insert script

use tempdb

go

Create table #Test1

(ID Int,

Name Varchar (50))

Insert into #Test1 Values (1,'A')

Insert into #Test1 Values (2,'B')

Insert into #Test1 Values (3,'C')

Insert into #Test1 Values (4,'B')

Insert into #Test1 Values (5,'E')

Insert into #Test1 Values (6,'F')

Insert into #Test1 Values (7,'G')

Insert into #Test1 Values (8,'H')

Insert into #Test1 Values (9,'I')

Insert into #Test1 Values (10,'J')

Insert into #Test1 Values (11,'K')

Insert into #Test1 Values (12,'L')

Insert into #Test1 Values (13,'M')

Insert into #Test1 Values (14,'N')

Insert into #Test1 Values (15,'O')

Insert into #Test1 Values (16,'P')

Insert into #Test1 Values (17,'Q')

Insert into #Test1 Values (18,'R')

Insert into #Test1 Values (19,'S')

Insert into #Test1 Values (20,'T')

Insert into #Test1 Values (20,'U')

Insert into #Test1 Values (20,'V')

Insert into #Test1 Values (20,'W')

Insert into #Test1 Values (20,'X')

Insert into #Test1 Values (20,'Y')

Insert into #Test1 Values (20,'Z')

Insert into #Test1 Values (20,'AA')

Insert into #Test1 Values (20,'AB')

Insert into #Test1 Values (20,'AC')

Insert into #Test1 Values (20,'AD')

Insert into #Test1 Values (20,'AE')

Insert into #Test1 Values (20,'AF')

Insert into #Test1 Values (20,'AG')

Insert into #Test1 Values (20,'AH')

Insert into #Test1 Values (20,'AI')

Insert into #Test1 Values (20,'AJ')

SELECT * from #Test1

Drop table #Test1

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

What is the logic here? Are you trying to have only 6 rows with a dynamic number of columns?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Below is a script that:

1) Creates the sample data

2) Queries the sample data for the desired results

-- (1) Let's build the table structure

DECLARE @source_table TABLE (ID int primary key, name varchar(2));

WITH

tens AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) x(n)),

itally(n) AS

(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM tens a CROSS JOIN tens b),

vals(val) AS

(SELECT CHAR(i1.n+64)+CHAR((i2.n+64)) AS val

FROM itally i1

JOIN itally i2

ON i1.n<=26 AND i2.n<=26

UNION

SELECT CHAR(i3.n+64)

FROM itally i3

WHERE i3.n<=26),

vals_filtered(n,val) AS

(SELECT ROW_NUMBER() OVER (ORDER BY LEN(val),val) AS n, val

FROM vals)

INSERT INTO @source_table

SELECT *

FROM vals_filtered

WHERE n<=36;

SELECT * FROM @source_table

-- (2) Let's get the desired results

SELECTx6.ID,

x6.name COL1,

x5.name COL2,

x4.name COL3,

x3.name COL4,

x2.name COL5,

x1.name COL5

FROM @source_table x1

LEFT JOIN @source_table x2 ON x1.ID=x2.ID+6

LEFT JOIN @source_table x3 ON x2.ID=x3.ID+6

LEFT JOIN @source_table x4 ON x3.ID=x4.ID+6

LEFT JOIN @source_table x5 ON x4.ID=x5.ID+6

LEFT JOIN @source_table x6 ON x5.ID=x6.ID+6

WHERE (x6.name+x5.name+x4.name+x3.name+x2.name) IS NOT NULL

AND x6.ID<=6

ORDER BY x6.ID

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 4

The Data I have in my Category code column in my source table is as below, as an example I just posted it as A B C D.

My data is

A

AA

AB

DEF

RDG

SDS

and so on

I just want them to divide by six rows and expecting the Columns to increase dynamically based on the count in the table. Appreciate you reply

Thanks

Srikanth

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

ksrikanth77 (8/8/2013)
The Data I have in my Category code column in my source table is as below, as an example I just posted it as A B C D.
My data is
A
AA
AB
DEF
RDG
SDS
and so on
I just want them to divide by six rows and expecting the Columns to increase dynamically based on the count in the table. Appreciate you reply
Thanks
Srikanth

What you are describing here is different than the sample data you posted above. You will need to explain in more detail exactly what you need here. Some more complete sample data perhaps...

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 6

Appreciate your reply which is perfectly matching with what I send earlier however Sorry as initially I thought I can manage if the I get the results which I mentioned earlier. It was my mistake providing a sample instead of the original structure.

Below is the original table

IF OBJECT_ID('TempDB..#Test1','U') IS NOT NULL

DROP TABLE #Test1

Create table #Test1

(Name Varchar (50))

Insert into #Test1 Values ('AAA')

Insert into #Test1 Values ('AEE')

Insert into #Test1 Values ('RHO')

Insert into #Test1 Values ('SRK')

Insert into #Test1 Values ('MMD')

Insert into #Test1 Values ('LOM')

Insert into #Test1 Values ('SDD')

Insert into #Test1 Values ('VIN')

Insert into #Test1 Values ('WI')

Insert into #Test1 Values ('WE')

Insert into #Test1 Values ('PD')

Insert into #Test1 Values ('Z')

Insert into #Test1 Values ('ZZZ')

Insert into #Test1 Values ('PRE')

Insert into #Test1 Values ('AEF')

Insert into #Test1 Values ('AGG')

Insert into #Test1 Values ('AJJ')

Insert into #Test1 Values ('BOM')

Insert into #Test1 Values ('ROM')

Insert into #Test1 Values ('FDD')

Insert into #Test1 Values ('ADD')

Insert into #Test1 Values ('WIR')

Insert into #Test1 Values ('PDK')

Insert into #Test1 Values ('ZOM')

Insert into #Test1 Values ('PPL')

Insert into #Test1 Values ('PRT')

Insert into #Test1 Values ('ART')

Insert into #Test1 Values ('AEW')

Insert into #Test1 Values ('AGR')

Insert into #Test1 Values ('AJO')

Insert into #Test1 Values ('AJL')

Insert into #Test1 Values ('AML')

Insert into #Test1 Values ('AOP')

Insert into #Test1 Values ('KEL')

Insert into #Test1 Values ('QWE')

Insert into #Test1 Values ('DRY')

Insert into #Test1 Values ('JOT')

SELECT * from #Test1

Drop table #Test1

Expecting output as in Six rows columns extending dynamically based on the Data in the table. Thanks in Advance.

COL1 COL2 COL3 COL4 COL5 COL6

AAASDDZZZFDDPRTAML

AEEVINPREADDARTAOP

RHOWIAEFWIRAEWKEL

SRKWEAGGPDKAGRQWE

MMDPDAJJZOMAJODRY

LOMZBOMPPLAJLJOT

Thanks

Srikanth

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

How do you define the order? do you have an identity value?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

ksrikanth77 (8/8/2013)
Expecting output as in Six rows columns extending dynamically based on the Data in the table. Thanks in Advance.
COL1 COL2 COL3 COL4 COL5 COL6
AAASDDZZZFDDPRTAML
AEEVINPREADDARTAOP
RHOWIAEFWIRAEWKEL
SRKWEAGGPDKAGRQWE
MMDPDAJJZOMAJODRY
LOMZBOMPPLAJLJOT

What is the purpose of this? This is a very strange requirement to say the least.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 9

If the order of the INSERT statements is to be respected, we need to add an IDENTITY column:

Create table #Test1

(Name Varchar (50),

pos int IDENTITY (1,1))

Here is a query for your precise data.

; WITH CTE AS (

SELECT Name, row_number() OVER (ORDER BY MIN(pos)) - 1 AS rowno

FROM #Test1

GROUP BY Name

)

SELECT MIN(CASE WHEN rowno / 6 = 0 THEN Name END) AS COL1,

MIN(CASE WHEN rowno / 6 = 1 THEN Name END) AS COL2,

MIN(CASE WHEN rowno / 6 = 2 THEN Name END) AS COL3,

MIN(CASE WHEN rowno / 6 = 3 THEN Name END) AS COL4,

MIN(CASE WHEN rowno / 6 = 4 THEN Name END) AS COL5,

MIN(CASE WHEN rowno / 6 = 5 THEN Name END) AS COL6

FROM CTE

GROUP BY rowno % 6

But if you add more rows and want more columns. Well, a query always return a finite set of columns, so that would have to be dynamic SQL.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 10

A BIG Thank you for the Help.Its Perfect solution for my requirement.