Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Hi Gurus,Can you please help me with this query.Just a single table I have a table called test with 5 columnsID, GRP,Country, Year, WageID WAGE GRP CNTY YR1 25000 A USA 19961 30000 A USA 19971 35000 A USA 19981 40000 A USA 19991 45000 A USA 20002 125000 A USA 19962 130000 A USA 19972 135000 A USA 19982 140000 A USA 19992 145000 A USA 200020 25000 A GER 199620 35000 A GER 199820 40000 A GER 199920 45000 A GER 200021 125000 A GER 199621 130000 A GER 199721 135000 A GER 199821 145000 A GER 2000As you can see for ID 21 there is no data for year 1999and for ID20 there is no data from year 1997.I have to write a Select statement to display above data and where ever records for a particular year is not present display NULL correspondingly.Appreciate your suggestionsThanks
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts
Posted - 2012-11-10 : 07:49:42
You should create a reference table that lists all the years that are of interest to you and then left join to that table. You could even construct one on the fly like this and use that:
SELECT t.id, t.wage, t.grp, t.cnty, c.YrFROM (VALUES (1996),(1997),(1998),(1999),(2000)) c(Yr) LEFT JOIN test t ON t.Yr = c.Yr