RS with two date columns from a single date column

Question

Post reply

RS with two date columns from a single date column

Brian Ki

SSC Enthusiast

Points: 147
More actions
May 2, 2012 at 1:50 pm

#268280

[not sure that Subject made sense but I couldn't think of a good description for this]
I have the following table (w/some sample data)
Date Value Tag
1/1/2012 100 Tag1
1/2/2012 200 Tag1
1/2/2012 150 Tag2
1/3/2012 250 Tag2
1/3/2012 300 Tag1
1/4/2012 350 Tag2
Tag represents the source of the Value - either 'Tag1' or 'Tag2'.
I need a recordset from this table for
Tag1Date Tag1Value Tag2Date Tag2Value
really, two sets of columns entirely separate from one another.
so for the above sample data it would be
1/1/2012 100 1/2/2012 150
1/2/2012 200 1/3/2012 250
1/3/2012 300 null null
null null 1/4/2012 350
I've tried what I know about SQL, but can't get it. Any ideas?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Chrissy321 SSCoach Points: 15764 More actions · Answer 1

I think you need a full self-join.

Since you did not provide DDL here is some air code....

SELECT

1.Date AS Tag1Date,

1.Value AS Tag1Value,

2.Date AS Tag2Date,

2.Value AS Tag2Value

FROM YourTable 1

FULL JOIN YourTable 2

ON 1.Date = 2.Date

Date is a reserved word so if you can change the name to something else, maybe TagDate.

drew.allen SSC Guru Points: 76995 More actions · Answer 2

Jeff Moden has a great article on this very subject. Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Brian Ki SSC Enthusiast Points: 147 More actions · Answer 3

cool thanks - I think a variation of the first reply has got me going. the Date join didn't work, but I added an Identity to the tables and FULL OUTER joined on that. The article may give me a better idea.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 4

If you now have data with something to guarantee the order, such as the IDENTITY column you spoke of, and you would save me some time by posting readily consumable test data according the the first link in my signature line below, I believe I can show you a fairly easy way to do this.

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

Brian Ki SSC Enthusiast Points: 147 More actions · Answer 5

Thanks for the tips - read the article - good info.

My 'solution' involved temp tables, so I'd welcome a faster solution. The example data below has 3 tags and the actual case will involve approx. 20,000 records for each tag (although not necessarily the same number for each tag). I mocked up a query for the desired RS.

--Test Table

DECLARE @data TABLE

(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

DateValue DATETIME,

BlockWeight DECIMAL(9,4),

Tagname VARCHAR(30))

--desired RS

Select '1/1/2012' as Tag1Date, 100 as Tag1Value, '1/1/2012' as Tag2Date, 150 as Tag2Value, '1/1/2012' as Tag3Date, 175 as Tag3Value

UNION ALL

Select '1/2/2012', 200, '1/3/2012', 250, '1/2/2012', 275 UNION ALL

Select '1/3/2012', 300, '1/4/2012', 350, '1/4/2012', 375 UNION ALL

Select '1/4/2012', 400, null, null, '1/5/2012', 475 UNION ALL

Select NULL, NULL, NULL, NULL, '1/6/2012', 575

--Test Data

INSERT @data

Select '1/1/2012', 100, 'Tag1' UNION ALL

Select '1/2/2012', 200, 'Tag1' UNION ALL

Select '1/3/2012', 300, 'Tag1' UNION ALL

Select '1/4/2012', 400, 'Tag1' UNION ALL

Select '1/1/2012', 150, 'Tag2' UNION ALL

Select '1/3/2012', 250, 'Tag2' UNION ALL

Select '1/4/2012', 350, 'Tag2' UNION ALL

Select '1/1/2012', 175, 'Tag3' UNION ALL

Select '1/2/2012', 275, 'Tag3' UNION ALL

Select '1/4/2012', 375, 'Tag3' UNION ALL

Select '1/5/2012', 475, 'Tag3' UNION ALL

Select '1/6/2012', 575, 'Tag3'

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 6

Using the data you provided, this will produce the correct output. I'll leave it up to you to format the dates.

WITH

cteEnumerate AS

( --=== Assign a numeric sequence to each tag group in the correct order

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY TagName ORDER BY DateValue),

DateValue,

BlockWeight,

TagName

FROM @data

) --=== "Pivot" the data using a bit of simple "Cross Tab" technology.

SELECT Tag1Date = MAX(CASE WHEN TagName = 'Tag1' THEN DateValue END),

Tag1Value = MAX(CASE WHEN TagName = 'Tag1' THEN BlockWeight END),

Tag2Date = MAX(CASE WHEN TagName = 'Tag2' THEN DateValue END),

Tag2Value = MAX(CASE WHEN TagName = 'Tag2' THEN BlockWeight END),

Tag3Date = MAX(CASE WHEN TagName = 'Tag3' THEN DateValue END),

Tag3Value = MAX(CASE WHEN TagName = 'Tag3' THEN BlockWeight END)

FROM cteEnumerate

GROUP BY RowNum

;

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

vinu512 SSCoach Points: 15729 More actions · Answer 7

This might help:

Select

(Case When Tag = 'Tag1' Then Date else NULL end) As Tag1Date,

(Case When Tag = 'Tag1' Then Value else NULL end) As Tag1Value,

(Case When Tag = 'Tag2' Then Date else NULL end) As Tag2Date,

(Case When Tag = 'Tag2' Then Value else NULL end) As Tag2Date

From Ex

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Brian Ki SSC Enthusiast Points: 147 More actions · Answer 8

Brian Ki

SSC Enthusiast

Points: 147

May 4, 2012 at 10:49 am

#1483696

worked like a charm - thanks.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 9

vinu512 (5/4/2012)
This might help:
Select
(Case When Tag = 'Tag1' Then Date else NULL end) As Tag1Date,
(Case When Tag = 'Tag1' Then Value else NULL end) As Tag1Value,
(Case When Tag = 'Tag2' Then Date else NULL end) As Tag2Date,
(Case When Tag = 'Tag2' Then Value else NULL end) As Tag2Date
From Ex

You really need to run that against some test data and see why that won't work as expected, Vinu.

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