Help Transposing SQL Table Pt3

Question

Post reply

Help Transposing SQL Table Pt3

carlton 84646

Ten Centuries

Points: 1026

July 2, 2022 at 8:02 pm

#4057982

Hello Community,

Can someone please help transpose a table.

The table currently looks like the following:

transposetable

I would like the table to look like the following:

transposetable2

Sample data is as follows:

CREATE TABLE temptable (
    OpportunityName nvarchar(100),
    CreatedOn datetime2,
    LastDateStatusChanged datetime2,
    CurrentOpportunityStatus nvarchar(50),
    MontaguOwner nvarchar(50),
    OpportunityDescription nvarchar(max))

INSERT temptable VALUES
(N'Incat International  [Legacy] ''01',CONVERT(DATETIME2, '2001-01-01 00:00:00.0000000', 121),CONVERT(DATETIME2, '2019-01-03 16:37:16.0000000', 121),N'Action - 5. Chopped',N'',N'(Legacy)Growing quickly but not really us - mainly a reseller.  Big merger with a US group in 2/2000.'),
(N'Dräger Safety ''07',CONVERT(DATETIME2, '2007-05-31 23:00:00.0000000', 121),CONVERT(DATETIME2, '2019-01-03 16:23:50.0000000', 121),N'Action - 5. Chopped',N'Peter Kroha',N'Fire protection and safety equipment business including gas masksVendor: DrägerwerkIdea Introduced By Company: DirectIdea Introduced By Individual: DirectType of Introducer: DirectSellside Advisor: -'),
(N'Apetito UK ''17',CONVERT(DATETIME2, '2017-03-28 09:05:00.0000000', 121),CONVERT(DATETIME2, '2019-02-07 11:43:46.0000000', 121),N'Post-Action - Chopped',N'Daniel Morgan',N'Apetito UK is the UK part of Apetito AG, a c.€500m ''meals on wheels'' provider. The UK business has a remarkably steady financial track record, growing every year since 1997 and now making £118m turnover and £24m EBITDA. There is a separate UK/Canada manager, Paul Freeston. The UK bit appears separable from the parent - there appears to be very little crossover in supply base, customer base and back office. Apetito also owns Wiltshire Farm Foods. Announced on 22nd March they are investing £31m in its facility in Trowbridge. Whilst ''meals on wheels'' doesn''t sound very Montagu, nutrition for old people is important to get right (and the right meals delivered on time every time), the logistics are complex and market drivers positive. Worth a call?'),
(N'Explore Learning ''12',CONVERT(DATETIME2, '2012-05-18 17:47:00.0000000', 121),CONVERT(DATETIME2, '2019-01-03 16:50:25.0000000', 121),N'Post-Action - Chopped',N'Alex Dabbous',N'Maths and English tuition centres for younger kids. Possibly too small for us (Skill Cap to check).')

SELECT * FROM temptable

As always, I really appreciate your help

Viewing 8 posts - 1 through 7 (of 7 total)

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

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 1

Hi, just to be clear, I'm not expecting the dates 04/01/2019

03/01/2019

09/12/2021

03/01/2019

To be in each Action. I was just being lazy by not putting the actual dates in each Action - but I'm sure you already knew that.

Jeff Moden SSC Guru Points: 1003289 More actions · Answer 2

None of the data between the two graphics matches each other nor does the test data. You need to be more clear and explain things like the other 4 statuses in the requested output.

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

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 3

Hi Jeff,

You are correct - this isn't very clear.

I'm going to rewrite this question. I have also realised that what I need is pivoting, not transposing.

Shall I just delete this question?

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 4

Hi All,

Please ignore this question. I don't know how to delete it.

I have a new question, which is based on Pivoting, not transposing..

Jeff Moden SSC Guru Points: 1003289 More actions · Answer 5

Not to worry, Carlton. They don't delete questions on this site except for SPAM. Thanks for the feedback.

The "pivoting" thing is actually pretty easy. In the old days, it was called "CROSS TAB" and MS used to have a section in BOL that explained it. Basically, it seems like you ultimately want to (in plain English), "Convert Rows to Columns".

With the understanding that the article doesn't explicitly explain how to "pivot" text items (use MAX as the aggregate function), it does explain (especially) why I thing Cross Tabs are so much better than Pivots.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1

There is a second article on the subject that explains how to make "dynamic" Cross Tabs, which can be very useful when the desired columns change or are otherwise unknown.

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

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

carlton 84646 Ten Centuries Points: 1026 More actions · Answer 6

Jeff,

I'm going to have to be become very proficient with Pivot (CROSS TABS) with my company.

Thanks for the links

Jeff Moden SSC Guru Points: 1003289 More actions · Answer 7

carlton 84646 wrote:

Jeff,
I'm going to have to be become very proficient with Pivot (CROSS TABS) with my company.
Thanks for the links

See my post on the other thread. I've also just updated that post with a second question. I can show you how to do that one because of the awesome test data you provided.

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