March 17, 2021 at 3:33 pm
Hello Geniuses,
I have a string manipulation task that I am not sure is even possible...
Here is the data
CREATE TABLE #TEMP
(
Id INT IDENTITY(1,1),
Sales Money,
StateCode VARCHAR(2)
)
INSERT INTO #TEMP (Sales, StateCode)
Select '1200', 'MA' UNION ALL
Select '200', 'MA' UNION ALL
Select '120', 'GA' UNION ALL
Select '190', 'CA' UNION ALL
Select '10', 'CA' UNION ALL
Select '200', 'MA' UNION ALL
Select '50', 'FL' UNION ALL
Select '100', 'FL' UNION ALL
Select '40', 'FL' UNION ALL
Select '300', 'MD' UNION ALL
Select '1200', 'GA' UNION ALL
Select '600', 'KY' UNION ALL
Select '400', 'KY' UNION ALL
Select '200', 'KY'
I am trying to get the top 3 states with the highest sales but listed as seen below;
TotalSalesByState. State. Top 3 State Sales
10000 GA CA - KY - MA --Assuming these are the top 3 states
2000 MA CA - KY. - MA
20000. KY CA - KY - MA
The top 3 states will be repeated throughout for that column.
Is this possible?
March 17, 2021 at 4:23 pm
I can't tell specifically what you want. Here's by best guess without further details from you:
;WITH cte_state_max_sales AS (
SELECT StateCode, MAX(Sales) AS Sales
FROM #TEMP
GROUP BY StateCode
),
cte_ranked_sales AS (
SELECT
*, ROW_NUMBER() OVER(PARTITION BY Sales ORDER BY StateCode) AS row_num
FROM cte_state_max_sales
)
SELECT
Sales,
STUFF(
ISNULL(' - ' + MAX(CASE WHEN row_num = 1 THEN StateCode END), '') +
ISNULL(' - ' + MAX(CASE WHEN row_num = 2 THEN StateCode END), '') +
ISNULL(' - ' + MAX(CASE WHEN row_num = 3 THEN StateCode END), '')
, 1, 3, '') AS [Top 3 States]
FROM cte_ranked_sales
GROUP BY Sales
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 17, 2021 at 6:35 pm
Thank you so much for your response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply