Rows to Columns WITHOUT a Pivot column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rows to Columns WITHOUT a Pivot column

Author

Topic

LaurieCox

158 Posts

Posted - 2010-07-20 : 12:52:15

I created the topic Complicated Pivot Problem for what I thought was a pivot problem. But now after reviewing the pivot clause and the two dynamic pivot examples that I was given in that topic, I am pretty sure the problem can't be solved with pivot.

From Dynamic Cross-Tabs/Pivot Tables:

quote:

The pivot column must be in the table (4). You can use an expression for the pivot column (a+b, LEFT(FirstName,3), etc.) as long as it can be derived from the table listed in (4). A cross-tab heading will be created for each distinct value in the pivot colum/expression.

In my case the Pivot column is NOT in the table. Nor do I want the cross tab columns created from the values of any column.

What follows is a simplified (from the original topic) description of the problem.

Given this data (data structure and sample data at end of post):


PATID Axis  Code
===== ====  ====
1000  1     PANIC
1000  1     DEPRESSION
1000  2     ANXIETY
1000  2     OCD
2000  1     DEPRESSION
2000  1     PANIC
2000  1     BIPOLAR
2000  2     OCD
2000  2     AUTISTIC

I want this output:

PATID  Axis1_1     Axis1_2     Axis1_3  Axis2_1  Axis2_2  Axis2_3
=====  =======     =======     =======  =======  =======  =======
1000   PANIC       DEPRESSION  [null]   ANXIETY  OCD      [null]
2000   DEPRESSION  PANIC       BIPOLAR  OCD      AUTISTIC [null]

This does not fit the pattern for a pivot (dynamic or not).

The rule is that for each row for a given PATID where Axis column is 1 the corresponding Code should be placed in the Axis1_x (where x is 1 to 3 in this example) column of the result table.

In the example above I don't care if the Axis1_1 or Axis1_2 has the value of PANIC or Depression. So this output would be perfectly acceptable:
[CODE]
PATID Axis1_1 Axis1_2 Axis1_3 Axis2_1 Axis2_2 Axis2_3
===== ======= ======= ======= ======= ======= =======
1000 DEPRESSION PANIC [null] ANXIETY OCD [null]
2000 DEPRESSION PANIC BIPOLAR OCD AUTISTIC [null]
[/CODE]
I do want all the [null] values to be at the end of any given set of columns.

And the same rule for Axis 2 (only the data goes into Axis2_x columns).
Data Structure and Sample data:


CREATE TABLE #SampleData (
    PATID int,
    Axis int,
    Code varchar(40))

INSERT INTO #SampleData
SELECT 1000, 1, 'PANIC' UNION ALL
SELECT 1000, 1, 'DEPRESSION' UNION ALL
SELECT 1000, 2, 'ANXIETY' UNION ALL
SELECT 1000, 2, 'OCD' UNION ALL
SELECT 2000, 1, 'DEPRESSION' UNION ALL
SELECT 2000, 1, 'PANIC' UNION ALL
SELECT 2000, 1, 'BIPOLAR' UNION ALL
SELECT 2000, 2, 'OCD' UNION ALL
SELECT 2000, 2, 'AUTISTIC'

Data Structure of the result table:


CREATE TABLE #ResultData (
    PATID int,
    Axis1_1 varchar(40) null,
    Axis1_2 varchar(40) null,
    Axis1_3 varchar(40) null,
    Axis2_1 varchar(40) null,
    Axis2_2 varchar(40) null,
    Axis2_3 varchar(40) null)

Personally after reading about pivot I don't think this problem is solvable with sql and that I need to manipulate the data in the application.

Though if somebody does comes up with a solution, I would be much appreciative.

Thanks,

Laurie

Edit: because my sample table and result table had the same name (the perils of copy and paste).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-20 : 13:47:34

This gets pretty close:

select *, ROW_NUMBER() over (partition by PatID, Axis order by PatID, Axis, Code) rownum 
into #z 
from #SampleData

exec sp_crosstab 'select PatID from #z group by PatID', 
'max(Code)', 
'''Axis'' + cast(Axis as varchar) + ''_'' + cast(rownum as varchar)',
'#z'

Note: sp_crosstab is the name of the dynamic cross tab procedure in the linked article, change as needed.

As far as ordering the nulls, if you look in the comments of the article there was a modification that ordered pivot columns, but I don't think it will work here.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-21 : 11:05:21

quote:

Personally after reading about pivot I don't think this problem is solvable with sql and that I need to manipulate the data in the application.

Though if somebody does comes up with a solution, I would be much appreciative.

Thanks,

Laurie

Edit: because my sample table and result table had the same name (the perils of copy and paste).

PAH!!!!!

Easy!


IF OBJECT_ID('tempDb..#sampleData') IS NOT NULL DROP TABLE #SampleData

CREATE TABLE #SampleData (
    PATID int,
    Axis int,
    Code varchar(40))

INSERT INTO #SampleData
SELECT 1000, 1, 'PANIC' UNION ALL
SELECT 1000, 1, 'DEPRESSION' UNION ALL
SELECT 1000, 2, 'ANXIETY' UNION ALL
SELECT 1000, 2, 'OCD' UNION ALL
SELECT 2000, 1, 'DEPRESSION' UNION ALL
SELECT 2000, 1, 'PANIC' UNION ALL
SELECT 2000, 1, 'BIPOLAR' UNION ALL
SELECT 2000, 2, 'OCD' UNION ALL
SELECT 2000, 2, 'AUTISTIC' 



SELECT
	[patId]
	, MAX(CASE WHEN [axis] = 1 AND [rowPos] = 1 THEN code ELSE NULL END) AS [Axis1_1]
	, MAX(CASE WHEN [axis] = 1 AND [rowPos] = 2 THEN code ELSE NULL END) AS [Axis1_2]
	, MAX(CASE WHEN [axis] = 1 AND [rowPos] = 3 THEN code ELSE NULL END) AS [Axis1_3]
	, MAX(CASE WHEN [axis] = 2 AND [rowPos] = 1 THEN code ELSE NULL END) AS [Axis2_1]
	, MAX(CASE WHEN [axis] = 2 AND [rowPos] = 2 THEN code ELSE NULL END) AS [Axis2_2]
	, MAX(CASE WHEN [axis] = 2 AND [rowPos] = 3 THEN code ELSE NULL END) AS [Axis2_3]
FROM
	(
	SELECT
		[PATID]
		, [AXIS]
		, code
		, ROW_NUMBER() OVER (PARTITION BY [patId], [axis] ORDER BY code) AS [RowPos]
	FROM
		#sampleData
	)
	raw_Data
GROUP BY
	[patId]

OFC -- whether you *SHOULD* do this in SQL is another matter entirely.

Results :


patId	Axis1_1	Axis1_2	Axis1_3	Axis2_1	Axis2_2	Axis2_3
1000	DEPRESSION	PANIC	NULL	ANXIETY	OCD	NULL
2000	BIPOLAR	DEPRESSION	PANIC	AUTISTIC	OCD	NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-21 : 11:11:10

Yeah, what he said.

You used the same alias names for the columns though.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-21 : 11:16:29

Cheers -- fixed.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

LaurieCox

158 Posts

Posted - 2010-07-21 : 16:04:43

Thanks for the responses.

Transact Charlie:

I am debating whether I should do this in sql or not. But you have given me something to work with.

Even if I don't end up going with the sql solution it might be fun to work on getting your solution to work on the full blown problem as described in the other thread (just as a learning exercise).

Again thanks for the help,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources