July 8, 2008 at 4:44 pm
Im having some trouble writing a query to return results the way i would like it.
The results are going to be used in a query that does data paging so I cannot have multiple rows for each company.
SELECT dbo.businessInfo.businessName, dbo.coupons.title, dbo.coupons.detail
FROM dbo.businessInfo INNER JOIN
dbo.coupons ON dbo.businessInfo.businessID = dbo.coupons.businessID
This query returns the data in this format
BusinessName | title | detail
---------------------------------------
company 1 | test 1 | detail 1
company 1 | test 2 | detail 2
company 1 | test 3 | detail 3
company 2 | test 4 | detail 4
etc
Im looking for a way to return the data in a format like this
BusinessName | title1 | detail1 | title2 | detail2 | title3 | detail 3
---------------------------------------------------------------------
company 1 | test 1 | detail 1 | test 2 | detail 2 | test 3 | detail 3
company 2 | test 4 | detail 4 | null | null | null | null
Any help would be appreciated.
Nerd, Geek, Online Guru, Programming Master! Need I say more?
July 8, 2008 at 10:00 pm
chazcross (7/8/2008)
Im having some trouble writing a query to return results the way i would like it.The results are going to be used in a query that does data paging so I cannot have multiple rows for each company.
SELECT dbo.businessInfo.businessName, dbo.coupons.title, dbo.coupons.detail
FROM dbo.businessInfo INNER JOIN
dbo.coupons ON dbo.businessInfo.businessID = dbo.coupons.businessID
This query returns the data in this format
BusinessName | title | detail
---------------------------------------
company 1 | test 1 | detail 1
company 1 | test 2 | detail 2
company 1 | test 3 | detail 3
company 2 | test 4 | detail 4
etc
Im looking for a way to return the data in a format like this
BusinessName | title1 | detail1 | title2 | detail2 | title3 | detail 3
---------------------------------------------------------------------
company 1 | test 1 | detail 1 | test 2 | detail 2 | test 3 | detail 3
company 2 | test 4 | detail 4 | null | null | null | null
Any help would be appreciated.
How many titles can there be for each company? How many do you want to show - or, do you want this to be dynamic?
I would recommend fixing the number of titles at a reasonable amount and allowing for a detail lookup for the company to get additional information.
One way to do that (there are several ways) is:
;WITH cte (businessID, title, detail, rn)
AS (
SELECT businessID
,title
,detail
,row_number() OVER (PARTITION BY businessID ORDER BY title)
FROM dbo.coupons
)
SELECT b.businessName
,c1.title AS Title1
,c1.detail AS Detail1
,c2.title AS Title2
,c2.detail AS Detail2
,c3.title AS Title3
,c4.detail AS Detail3
FROM dbo.businessInfo AS b
LEFT JOIN cte c1 ON c1.businessID = b.businessID AND c1.rn = 1
LEFT JOIN cte c2 ON c2.businessID = b.businessID AND c2.rn = 2
LEFT JOIN cte c3 ON c3.businessID = b.businessID AND c3.rn = 3
This is un-tested and may not even be the best approach, but should give you the desired results.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 9, 2008 at 12:49 am
chazcross (7/8/2008)
Im having some trouble writing a query to return results the way i would like it.
It's important to know how many titles you might have per company... if it's a small number, we can hardcode... if not, we need to go dynamic.
So, how many titels per company? 🙂
--Jeff Moden
July 9, 2008 at 8:57 am
The companies are allowed up to 3 coupons, So 3 titles.
Im going to try the method posted above and let you know the results.
Nerd, Geek, Online Guru, Programming Master! Need I say more?
July 9, 2008 at 10:35 am
Thanks Jeffrey Williams that solution worked out great.
Nerd, Geek, Online Guru, Programming Master! Need I say more?
July 9, 2008 at 11:08 am
Glad I could help, now for future reference - please read the following article:
Best Practices: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
This article will show you how to post your questions so it is much easier for someone to provide a tested solution.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 9, 2008 at 6:35 pm
Here's another way using one of the "new" functions in SQL Server 2005 and a Classic Cross Tab... this example would require two Pivot clauses and a join or two, so I didn't use PIVOT...
Also note that the first half of this code is just setting up for a demonstration and is not required for the production code.
[font="Courier New"]--=======================================================================
-- Create and populate a temporary test table...
-- THIS IS NOT A PART OF THE SOLUTION!
-- Please note that this is the way data should be posted for
-- future posts. See the Link in my signature for how...
--=======================================================================
--===== Conditionally drop the test table
IF OBJECT_ID('TempDB..#Coupons','U') IS NOT NULL
DROP TABLE #Coupons
--===== Create the table
CREATE TABLE #Coupons
(BusinessName VARCHAR(10),
Title VARCHAR(10),
Detail VARCHAR(10))
--===== Populate the test table with data
INSERT INTO #Coupons
(BusinessName,Title,Detail)
SELECT 'Company 1','Test 1','Detail 1' UNION ALL
SELECT 'Company 1','Test 2','Detail 2' UNION ALL
SELECT 'Company 1','Test 3','Detail 3' UNION ALL
SELECT 'Company 2','Test 4','Detail 4' UNION ALL
SELECT 'Company 3','Test 5','Detail 5' UNION ALL
SELECT 'Company 3','Test 6','Detail 6' UNION ALL
SELECT 'Company 4','Test 7','Detail 7' UNION ALL
SELECT 'Company 4','Test 8','Detail 8' UNION ALL
SELECT 'Company 4','Test 9','Detail 9'
--=======================================================================
-- Demonstrate a solution using a classic Cross Tab
--=======================================================================
;WITH
cteItems AS
(--==== Assign a "rank" as an ItemNum to each row
SELECT ROW_NUMBER() OVER (PARTITION BY BusinessName
ORDER BY BusinessName,Title,Detail) AS ItemNum,
BusinessName,
Title,
Detail
FROM #Coupons
)--==== Create a cross tab based on ItemNum...
SELECT BusinessName,
MAX(CASE WHEN ItemNum = 1 THEN Title ELSE '' END) AS Title1,
MAX(CASE WHEN ItemNum = 1 THEN Detail ELSE '' END) AS Detail1,
MAX(CASE WHEN ItemNum = 2 THEN Title ELSE '' END) AS Title2,
MAX(CASE WHEN ItemNum = 2 THEN Detail ELSE '' END) AS Detail2,
MAX(CASE WHEN ItemNum = 3 THEN Title ELSE '' END) AS Title3,
MAX(CASE WHEN ItemNum = 3 THEN Detail ELSE '' END) AS Detail3
FROM cteItems
GROUP BY BusinessName[/font]
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply