list multiple rows as columns
-
Ignoring the utility of doing something like this, I has given the user a view like
CREATE TABLE [dbo].[result] (
[last] [varchar] (20) NULL ,
[first] [varchar] (20) NULL ,
[gender] [char] (1) NULL ,
[testname] [varchar] (10) NULL ,
[question#] [numeric](2, 0) NULL ,
[questionText] [varchar] (30) NULL ,
[answer] [varchar] (5) NULL
) ON [PRIMARY]
INSERT INTO
[dbo].[result] VALUES('smith','joe','m','test1',1,'are you','yes')INSERT INTO [dbo].[result] VALUES('smith','joe','m','test1',2,'will you','no')INSERT INTO [dbo].[result] VALUES('smith','joe','m','test1',3,'have you','yes')INSERT INTO [dbo].[result] VALUES('smith','joe','m','test2',1,'know stuff','no')INSERT INTO [dbo].[result] VALUES('best','jane','f','test1',1,'are you','yes')INSERT INTO [dbo].[result] VALUES('best','jane','f','test1',2,'will you','yes')INSERT INTO [dbo].[result] VALUES('best','jane','f','test1',3,'have you','yes')INSERT INTO [dbo].[result] VALUES('best','jane','f','test2',1,'know stuff','no')
Rather than display the data like:
last first gender testname question# questionText answer
smith joe m test1 1 are you yes
smith joe m test1 2 will you no
smith joe m test1 3 have you yes
smith joe m test2 1 know stuff no
best jane f test1 1 are you yes
best jane f test1 2 will you yes
best jane f test1 3 have you yes
best jane f test2 1 know stuff no
The user wants to see one row for each name (in this case 2 rows) with all data pertaining to that row so they want:
smith joe m test1 1 are you yes test1 2 will you no test1 3 have you yes test2 1 know stuff no
best jane f test1 1 are you yes test1 2 will you yes test1 3 have you yes test2 1 know stuff no
This really isn't the same thing as a pivot since they don't want the row to become a column name with some aggregate function showing the sum or a count. They just want the column data moved over and pasted in the same row.
Francis
-
You'll take one look at this and think I'm as crazy as a cyclist on steroids... but try it... no real loop, nasty fast, and does not require a function...
--===== If temp working table exists, drop it
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
--====== Create the temp working table
CREATE TABLE #MyHead
(
TestName VARCHAR(10) NOT NULL,
[Question#] VARCHAR(2) NOT NULL,
Part1 VARCHAR(43) NOT NULL,
Part2 VARCHAR(37) NOT NULL,
DesiredCol VARCHAR(7920)
CONSTRAINT PK_MyHead_Composite
PRIMARY KEY CLUSTERED (Part1,TestName,[Question#])
)
--===== Populate the temp working table with some preconfiged data
INSERT INTO #MyHead
(TestName,[Question#],Part1,Part2,DesiredCol)
SELECT TestName,[Question#],
Last+' '+First+' '+Gender AS Part1,
TestName+' '+CAST([Question#] AS VARCHAR(10))+' '+QuestionText+' '+Answer AS Part2,
'' AS DesiredCol
FROM Result
ORDER BY Part1,TestName,[Question#]
--===== Declare some local variables for the trick-update to come
DECLARE @StringWork VARCHAR(7920)
DECLARE @MyPart1 VARCHAR(43)
SET @MyPart1 = ''
--===== This runs faster than any other kind of concatenation I know
UPDATE #MyHead
SET @StringWork = DesiredCol = CASE WHEN @MyPart1 = Part1
THEN @StringWork+' '+Part2
ELSE Part1+' '+Part2
END,
@MyPart1 = Part1
--===== Display the desired results
SELECT MAX(DesiredCol)
FROM #MyHead
GROUP BY Part1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Very nice. I didn't think of creating a big string. I believe I can work with this. Ultimately the user wants this in an Excel spreadsheet so all I need to do is add in come commas where I want the columns and save it in a csv file. I apreciate the work. Its a good idea Thanks
Francis
-
Thank you for the feedback. If you add tabs instead of commas, it'll open up in Excel without much of a conversion at all. Didn't realize that the target of this was going to be a spreadsheet but, as you say, add a couple of well placed delimiters and you're in business.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply