How to transform table with few rows to one row?

Question

Post reply

How to transform table with few rows to one row?

Jagger-784276

SSC Eights!

Points: 808
More actions
March 21, 2010 at 1:10 pm

#139514

Hi,

I am beginner.Just an example. If I have table with 3 rows and 3 columns:

customer number notes last visit date

100000 text 1 10.feb.2010

100000 text 2 10.feb.2010

100000 text 3 10.feb.2010

How to make customer number with only one row like this:

100000 text 1 text 2 text 3 10.feb.2010

thanks?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Jagger-784276 (3/21/2010)

Hi,

I am beginner.Just an example. If I have table with 3 rows and 3 columns:

customer number notes last visit date

100000 text 1 10.feb.2010

100000 text 2 10.feb.2010

100000 text 3 10.feb.2010

How to make customer number with only one row like this:

100000 text 1 text 2 text 3 10.feb.2010

thanks?

Uh huh... what happens if there are 4 rows or only 2? What happens when there are 20?

What you need is a dynamic cross-tab. Please see the following 2 articles (1st teaches the cross tab method, second teaches how to make it dynamic) and use MAX instead of SUM.

http://qa.sqlservercentral.com/articles/T-SQL/63681/

http://qa.sqlservercentral.com/articles/Crosstab/65048/

I'll also tell you that you need to number the rows with ROW_NUMBER before you can pull this off.

--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

Jagger-784276 SSC Eights! Points: 808 More actions · Answer 2

Hi, thanks for your answer.

What is my problem?

Instead of numbers I have notes.

Each customer has some notes. Sometimes it is one note, sometimes more than one on the same date.

That is my probmem. For example like mine one customer has one visit on 10.feb.2010 but with 3 notes.

It makes that I have 3 rows in table. Instead to have 3 rows I'd like to have it in one row.

like this 100000, 10.feb.2010, txt1, txt2, txt3.

Your instruction about:

SELECT Year,

SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],

SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],

SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],

SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],

SUM(Amount) AS Total FROM #SomeTable1 GROUP BY Year

I completely understand this but what to do in my case.

I have to make :

case when Notes ??? exist??

Hot to make this command?? How to group by If I don't have sum or count?

My notes are some sentences. I can't sum or count notes ( sentences or some text )!

here u r:

use databasename

go

CREATE TABLE TEST2

(

Customernumber int NOT NULL,

LastVisitDate datetime NOT NULL,

Note varchar(200) NOT NULL,

Nr smallint NULL

)

GO

INSERT INTO TEST2

(Customernumber, LastVisitDate, Note,Nr)

SELECT 100000, N'10.feb.2010', N'New York',1 UNION ALL

SELECT 100000, N'10.feb.2010', N'London',2 UNION ALL

SELECT 100000, N'10.feb.2010', N'Paris',3

go

"never know what would be under attribute note"!!!

thank you

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Jagger-784276 (3/23/2010)

How to group by If I don't have sum or count?

Use MAX instead.

--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