July 2, 2022 at 8:02 pm
Hello Community,
Can someone please help transpose a table.
The table currently looks like the following:
I would like the table to look like the following:
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
July 2, 2022 at 11:19 pm
July 2, 2022 at 11:31 pm
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
Change is inevitable... Change for the better is not.
July 3, 2022 at 7:49 am
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?
July 3, 2022 at 7:50 am
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..
July 3, 2022 at 5:48 pm
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
Change is inevitable... Change for the better is not.
July 3, 2022 at 6:02 pm
Jeff,
I'm going to have to be become very proficient with Pivot (CROSS TABS) with my company.
Thanks for the links
July 3, 2022 at 6:07 pm
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy