Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Could this be done using PIVOT ? Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:39 AM
Points: 11, Visits: 64
I have 2 identical tables one contains current settings, the other contains all historical settings.
I could create a union view to display the current values from tableA and all historical values from tableB, but
that would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
Post #1568067
Posted Friday, May 9, 2014 2:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 34, Visits: 327
Budd,

I am not sure what you are trying to acheive. When you say "one joined or conditonal statement" what would the condition be? If it is a particular ID from Table A, then you would have to pass that as a parameter or variable. If you want to want all values from both tables, that is the statement you have without the WHERE clauses.

If you can explain more about what it is you want I should be able to help.

Regards,

Ash
Post #1569180
Posted Friday, May 9, 2014 2:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:00 AM
Points: 6,917, Visits: 6,990
What does tblid represent?
How many previous settings rows are there for a tblid?
How do you want the output to look like (Why PIVOT?)

Please supply table DDL, sample data and expected output.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1569182
Posted Friday, May 9, 2014 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 12,993, Visits: 12,407
Budd (5/6/2014)
I have 2 identical tables one contains current settings, the other contains all historical settings.
I could create a union view to display the current values from tableA and all historical values from tableB, but
that would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid


As David said providing ddl and sample data would be a big help.

Why not just use a left join?

DECLARE @tblid int = 501
SELECT ta.[Columns], tb.[Columns]
FROM TableA ta
left join TableB tb on tb.tblid = ta.tblid
where ta.tblid = @tblid

Everything in ta is current and the values in tb are previous.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1569260
Posted Friday, May 9, 2014 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:39 AM
Points: 11, Visits: 64
OK, I should not have suggested the possibility of using PIVOT for this, I just did not know what to put in the subject line, and maybe this was Not as well explained as I had thought.
Here is a sample including table layouts data and a UNION select query that demonstrates one way that I am able to achieve the correct results.
My Question; Can this be done without using a UNION, How, and Which is the best? After all this is Microsoft there's always more than one way to do it.


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE dbo.TableA(
tblid INT IDENTITY(1,1) NOT NULL,
fld1 varchar(200) NOT NULL,
fld2 varchar(200) NOT NULL,
fld3 varchar(200) NOT NULL,
fld4 DATETIME NOT NULL)

CREATE TABLE dbo.TableA_hist(
tblid INT NOT NULL,
fld1 varchar(200) NOT NULL,
fld2 varchar(200) NOT NULL,
fld3 varchar(200) NOT NULL,
fld4 DATETIME NOT NULL,
CreDT DATETIME NOT NULL) --DEFAULT getdate() This would be the normal setting

SET IDENTITY_INSERT [TableA] OFF
INSERT INTO dbo.TableA
VALUES('App Setting For Reviews','TRUE','Hulk, Jim','2014-05-06 12:55:32.307')
SET IDENTITY_INSERT [TableA] ON
INSERT INTO dbo.TableA_hist VALUES
(1,'App Setting For Reviews','TRUE','Hulk, Jim','2014-05-06 09:31:53.820','2014-05-06 12:55:03.460'),
(1,'App Setting For Reviews','FALSE','Hulk, Jim','2014-05-06 09:25:13.543','2014-05-06 09:31:36.060'),
(1,'App Setting For Reviews','TRUE','Hulk, Jim','2014-05-06 09:19:03.287','2014-05-06 09:24:54.933'),
(1,'App Setting For Reviews','FALSE','Norris, Chuck','2014-05-06 08:18:30.763','2014-05-06 09:18:49.020'),
(1,'App Setting For Reviews','TRUE','Kent, Clark','2013-08-09 08:30:41.273','2014-05-06 08:48:46.580')

DECLARE @tblid int = 1
SELECT fld1,fld2,fld3,fld4,CreDT,'PreviosSetting'
FROM TableA_hist tb
WHERE tblid = @tblid
UNION
SELECT fld1,fld2,fld3,fld4,0,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
ORDER BY fld4 desc


DROP TABLE dbo.TableA
DROP TABLE dbo.TableA_hist
Post #1569268
Posted Friday, May 9, 2014 7:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 34, Visits: 327
Budd I think that is the perfect way to do what you want. Why don't you want to use the UNION clause?

Ash
Post #1569274
Posted Friday, May 9, 2014 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 12,993, Visits: 12,407
I have a feeling the reason is because of performance? Have you tried changing your UNION to UNION ALL? It seems that since you have a date in there you that all rows are effectively unique. Make sure you understand the difference before you use this though.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1569298
Posted Friday, May 9, 2014 8:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:39 AM
Points: 11, Visits: 64
Simply looking for alternatives and better performance.
I have gotten myself into a bad habit of only using UNION to avoid duplicates and had quickly brushed that thought away. As I think it over now I can see how the additional over head of filtering out duplicates could be costly, but now that you bring that to my attention I've run a little simple testing. When I run both UNION and UNION ALL at the same time and review the execution plans, they are both at 50%. If this is because the size of the selection is not enough to matter, how to I determine the size (aka volume of data) that it will take to make a difference between the 2?
But that is really another question and I am still open to ideas of different queries to produce the same results.
Post #1569317
Posted Friday, May 9, 2014 8:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 34, Visits: 327
Budd,

I think with such a simple query performance would be best improved with good use of indexing. If possible create a clustered index on the column in your WHERE clause, or if that is not possible then a non-clustered index with the other columns in your SELECT clause as included columns.

Hope that helps,

Ash
Post #1569322
Posted Friday, May 9, 2014 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 12,993, Visits: 12,407
Budd (5/9/2014)
Simply looking for alternatives and better performance.
I have gotten myself into a bad habit of only using UNION to avoid duplicates and had quickly brushed that thought away. As I think it over now I can see how the additional over head of filtering out duplicates could be costly, but now that you bring that to my attention I've run a little simple testing. When I run both UNION and UNION ALL at the same time and review the execution plans, they are both at 50%. If this is because the size of the selection is not enough to matter, how to I determine the size (aka volume of data) that it will take to make a difference between the 2?
But that is really another question and I am still open to ideas of different queries to produce the same results.


It is not about when or when not to use UNION ALL it is about knowing what is appropriate for the results. If you have a result set where duplicates are ok (or each row is unique) you should use UNION ALL. Not because it is acceptable here but because it will perform better. It performs better because it does not have to check for uniqueness. On a small dataset it isn't likely to make much (if any) measurable difference.

When tackling performance problems you need to look at the whole picture. There are a lot of factors that come into play when determining performance improvements.

If this was my query I would almost certainly use a UNION or UNION ALL for this type of thing.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1569326
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse