Splitt values from one column in two rows

Question

Splitt values from one column in two rows

csyz

SSC Journeyman

Points: 96
More actions
December 14, 2016 at 7:19 am

#332813

Dear NG
I have following issue:
2 Tables (Zusatzfeld, Vorschuss)
dbo.Zusatzfeld
Bold_ID (PK)
WertDatum (the one with the values)
MetaZusatzfeld (the one with Searchvalue)
UserEintrag (connection to dbo.Vorschuss)
dbo.Vorschuss
Bold_ID (PK)
Description (one column with the value)
Projekt (one column with the value)
the goal is to get a new table with following values:
Bold_ID from dbo.Vorschuss, Description from dbo.Vorschuss, StartDate from dbo.Zusatzfeld (Value StartDate from WertDatum), EndDate from dbo.Zusatzfeld (Value EndDate from Wertdatum), Projekt from dbo.Vorschuss
540060, Vorschuss Juli, 2012-07-01, 2012-07-31, 3526
Does anybody know how to build the query? Any help is welcome... 🙂
Best regards Chris

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

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

sgmunson SSC Guru Points: 110651 More actions · Answer 1

csyz (12/14/2016)
Dear NG
I have following issue:
2 Tables (Zusatzfeld, Vorschuss)
dbo.Zusatzfeld
Bold_ID (PK)
WertDatum (the one with the values)
MetaZusatzfeld (the one with Searchvalue)
UserEintrag (connection to dbo.Vorschuss)
dbo.Vorschuss
Bold_ID (PK)
Description (one column with the value)
Projekt (one column with the value)
the goal is to get a new table with following values:
Bold_ID from dbo.Vorschuss, Description from dbo.Vorschuss, StartDate from dbo.Zusatzfeld (Value StartDate from WertDatum), EndDate from dbo.Zusatzfeld (Value EndDate from Wertdatum), Projekt from dbo.Vorschuss
540060, Vorschuss Juli, 2012-07-01, 2012-07-31, 3526
Does anybody know how to build the query? Any help is welcome... 🙂
Best regards Chris

Please learn how to use the IFCode tags so that you can post table create statements with insert statements, that are easily consumable and easily copied and pasted into SSMS, because without that, it's much more difficult and time-consuming for folks to help you.

That said, we will also need to know if the values in MetaZusatzfeld will always be "StartDate" and "EndDate", or if they could be a wide variety of values. The latter is going to be a pretty seriously difficult task, that may be beyond the reasonable efforts of volunteers posting to this site, and may be better served by a paid consultant. The latter will require some seriously fancy dynamic SQL.

Also, what criteria will select the records from the first table?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could use a cross tabs approach. It's simple and should give you what you need.

CREATE TABLE dbo.Zusatzfeld(

Bold_ID int,

WertDatum datetime,

MetaZusatzfeld varchar(20),

UserEintrag int);

CREATE TABLE dbo.Vorschuss(

Bold_ID int,

Description varchar(100),

Projekt int);

INSERT INTO Zusatzfeld

VALUES

(540058, '20120701', 'StartDate', 540060),

(540059, '20120731', 'EndDate', 540060);

INSERT INTO Vorschuss

VALUES

(540060, 'Vorschuss Juli 2012', 3526);

SELECT v.Bold_ID,

v.Description,

MAX( CASE WHEN z.MetaZusatzfeld = 'StartDate' THEN WertDatum END) AS StartDate,

MAX( CASE WHEN z.MetaZusatzfeld = 'EndDate' THEN WertDatum END) AS EndDate,

v.Projekt

FROM Zusatzfeld z

JOIN Vorschuss v ON z.UserEintrag = v.Bold_ID

GROUP BY v.Bold_ID,

v.Description,

v.Projekt;

GO

DROP TABLE Zusatzfeld, Vorschuss;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 77014 More actions · Answer 3

It looks like a simple pivot/crosstab. Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sgmunson SSC Guru Points: 110651 More actions · Answer 4

drew.allen (12/14/2016)
It looks like a simple pivot/crosstab. Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Drew

Yes, but that assumes that the values "StartDate" and "EndDate" are always going to be those two values. They may or may not be constants.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

drew.allen SSC Guru Points: 77014 More actions · Answer 5

sgmunson (12/14/2016)
drew.allen (12/14/2016)
It looks like a simple pivot/crosstab. Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Drew
Yes, but that assumes that the values "StartDate" and "EndDate" are always going to be those two values. They may or may not be constants.

It's Occam's Razor. Assume that the simplest solution is the best unless proven otherwise. With the available data, a crosstab/pivot will work, so there's no reason--yet--to investigate a more complex solution.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA