April 28, 2014 at 1:23 pm
Can someone please give me advise on a better way to write the following?
SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4
I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.
Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.
April 28, 2014 at 3:24 pm
robin.pryor (4/28/2014)
Can someone please give me advise on a better way to write the following?SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4
I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.
Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.
Look at what you posted and ask yourself if you honestly think anybody can offer any real advice here. We have nothing but a vague query that sort of represents your actual query. We have no basis for what your are trying to do at all.
Maybe a cross tab would work here? Take a look at the links in my signature. Hard to say unless you give us some details.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 28, 2014 at 3:45 pm
It really looks like a CROSS TABS problem. Read the articles that Sean mentioned and remember that you can use MAX() for strings.
April 29, 2014 at 5:13 am
If your solution is bad then anything we suggest may not be better, e.g.
SELECT b.BoxId,
MAX(CASE WHEN w.parameter = 1 THEN w.field1 END) AS [value1],
MAX(CASE WHEN w.parameter = 2 THEN w.field1 END) AS [value2],
MAX(CASE WHEN w.parameter = 3 THEN w.field1 END) AS [value3],
MAX(CASE WHEN w.parameter = 4 THEN w.field1 END) AS [value4]
FROM boxes b
LEFT OUTER JOIN widgets w ON w.boxid = b.boxid
GROUP BY b.BoxId
But the performance may be worse
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2014 at 12:15 pm
You probably want to do the grouping in the inner query rather than the outer query. Also, can limit parameter values to 1-4 in the inner query itself.
SELECT a.BoxId, b.value1, b.value2, b.value3, b.value4
FROM boxes a
LEFT OUTER JOIN (
SELECT boxid,
MAX(CASE WHEN parameter = 1 THEN field1 END) AS value1,
MAX(CASE WHEN parameter = 2 THEN field1 END) AS value2,
MAX(CASE WHEN parameter = 3 THEN field1 END) AS value3,
MAX(CASE WHEN parameter = 4 THEN field1 END) AS value4
FROM widgets
WHERE
parameter BETWEEN 1 AND 4
GROUP BY boxid
) AS b ON a.boxid = b.boxid
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 30, 2014 at 3:03 am
to fix my previous script error
SELECT a.BoxId, MAX(b.field1) as value1 , MAX(c.field1) as value2, MAX(d.field1) as value3, MAX(e.field1) as value4
FROM boxes a LEFT JOIN widgets bb ON a.boxid = bb.boxid AND bb.parameter IN ( 1,2,3,4 )
OUTER APPLY ( SELECT bb.field1 WHERE bb.parameter = 1 ) b
OUTER APPLY ( SELECT bb.field1 WHERE bb.parameter = 2) c
OUTER APPLY ( SELECT bb.field1 WHERE bb.parameter = 3) d
OUTER APPLY( SELECT bb.field1 WHERE bb.parameter = 4) e
GROUP BY a.BoxId
still can't give the same return with the original script ,just to correct my errors .sorry for my fault
April 30, 2014 at 9:07 am
rock.liu (4/30/2014)
can you use this one ?SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
cross apply (select top 1 b.field1 from widgets b ON a.boxid = b.boxid and b.parameter = 1 ) b
cross apply (select top 1 c.field1 from widgets c ON a.boxid = c.boxid and c.parameter = 2 ) c
cross apply (select top 1 d.field1 from widgets d ON a.boxid = d.boxid and d.parameter = 3 ) d
cross apply (select top 1 e.field1 from widgets e ON a.boxid = b.boxid and e.parameter = 4 ) e
Your code is full of errors and it does not guarantee to return the same results as the original query. You should use outer apply. And the problem remains the same, you're reading widgets table four times.
May 2, 2014 at 10:53 am
Sean Lange (4/28/2014)
robin.pryor (4/28/2014)
Can someone please give me advise on a better way to write the following?SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4
I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.
Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.
Look at what you posted and ask yourself if you honestly think anybody can offer any real advice here. We have nothing but a vague query that sort of represents your actual query. We have no basis for what your are trying to do at all.
Maybe a cross tab would work here? Take a look at the links in my signature. Hard to say unless you give us some details.
Sorry about the vagueness. I did the best I could. I work for a govt agency and the particulars of this specific thing involve sensitive data and field names that completely give away where I work. Call it paranoia, but my "company" would crap if I put the real structure out there. Do the real field and table names really matter? What I used, while meaningless, could really exist as a table.
May 2, 2014 at 11:11 am
robin.pryor (5/2/2014)
Sean Lange (4/28/2014)
robin.pryor (4/28/2014)
Can someone please give me advise on a better way to write the following?SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
LEFT OUTER JOIN widgets b ON a.boxid = b.boxid and b.parameter = 1
LEFT OUTER JOIN widgets c ON a.boxid = c.boxid and b.parameter = 2
LEFT OUTER JOIN widgets d ON a.boxid = d.boxid and b.parameter = 3
LEFT OUTER JOIN widgets e ON a.boxid = b.boxid and b.parameter = 4
I'm likely giving a horrible example, but trust me, the real thing is way way way uglier.
Due to an interface that already exists, the big requirement is that I HAVE to end up with those 4 columns.
Look at what you posted and ask yourself if you honestly think anybody can offer any real advice here. We have nothing but a vague query that sort of represents your actual query. We have no basis for what your are trying to do at all.
Maybe a cross tab would work here? Take a look at the links in my signature. Hard to say unless you give us some details.
Sorry about the vagueness. I did the best I could. I work for a govt agency and the particulars of this specific thing involve sensitive data and field names that completely give away where I work. Call it paranoia, but my "company" would crap if I put the real structure out there. Do the real field and table names really matter? What I used, while meaningless, could really exist as a table.
No of course the real names don't make any difference at all. The problem is that our common language is sql and since we didn't have tables or sample data to work with we have to guess. Notice you have several attempts which may or may not work. Nobody can actually test it because we didn't have tables to work with. While nobody really knows the answer it does seem to be the general consensus that a cross tab is what you want. Did the articles I referenced help?
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2014 at 11:18 am
Yes. Thank you
May 2, 2014 at 11:38 am
Real column names or data don't matter. Real data types matter and the structure of the data as well to be sure that we can give an accurate solution.
May 2, 2014 at 11:56 am
here is a simple set up script....please use this as a method to provide suitable data for us to help you.
USE [tempdb]
GO
CREATE TABLE [dbo].[boxes](
[boxid] [int] NOT NULL
)
CREATE TABLE [dbo].[widgets](
[boxid] [int] NOT NULL,
[field1] [varchar](50) NULL,
[parameter] [int] NULL
)
INSERT [dbo].[boxes] ([boxid]) VALUES (1)
INSERT [dbo].[boxes] ([boxid]) VALUES (2)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (1, N'ringo', 1)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (1, N'john', 2)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (1, N'paul', 3)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (1, N'george', 4)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (1, N'whoknows', 5)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (2, N'bilbo', 1)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (2, N'frodo', 2)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (2, N'orcs', 3)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (2, N'elvee', 4)
INSERT [dbo].[widgets] ([boxid], [field1], [parameter]) VALUES (2, N'gandalf', 5)
select * from boxes
select * from widgets
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 2, 2014 at 12:02 pm
So, [widgets] looks like an EAV (entity attribute value) table. This is similar to the common scenario where users request a query containing CustomerID, HomePhone, CellPhone, WorkPhone and all phone numbers are contained in the same table.
You did say that the actual table or query is uglier, but looking at the simplified example you've provided, I'm pretty sure that [widgets] should have a primary key on box + parameter to insure there are no duplicates on that key and reduce the complexity required for the query. Also an additional non-clustered composite index on boxid + parameter + field1 should cover the join(s). Read up on "covering indexes and joins". If this is a large table, then indexing properly is more important than how exactly you construct this query.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply