Pivot the data
-
I am wondering if I can get some help here. I am joining 2 tables which brings me the result set I want. It gives me the list of state and the list of company name. Please see an attachment. This is what I am trying to do. I need to have State values as columns and under those columns, I will have a company name. See attachment please. Is this even possible?
-
You've been around long enough to know that's not the way to post sample data and expected results. If you need a refresher, look in my signature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - NewBornDBA2017 - Monday, July 23, 2018 10:50 AM
The easiest way to do this would be to use an SSRS report, and use a matrix instead of a tablix.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Please repost your sample data as Table DDL and DML. A picture may be worth a thousand words but it's useless for posting to a SQL forum.
-
I'll apologize up front for not adding comments. I'm crunched for time and I wanted to post a solution.
If you have questions, I'll comeback and re-post the solution with comments and a deeper explanation of how the code works (if someone hasn't beaten me to it).
Also, there is probably a more efficient solution that doesn't require separate selects for each state... Unfortunately my current time crunch won't allow me to test multiple solutions right now.IF OBJECT_ID('tempdb..#States', 'U') IS NULL
BEGIN -- DROP TABLE #States;
CREATE TABLE #States (
StateCode CHAR(2) NOT NULL PRIMARY KEY CLUSTERED,
StateName VARCHAR(100) NOT NULL
);INSERT #States (StateCode, StateName) VALUES
('AL', 'Alabama'), ('AZ', 'Arizona'), ('CA', 'California');
END;IF OBJECT_ID('tempdb..#StateCompany', 'U') IS NULL
BEGIN -- DROP TABLE #StateCompany;
CREATE TABLE #StateCompany (
StateCode CHAR(2),
CompanyName VARCHAR(100),
PRIMARY KEY CLUSTERED (StateCode, CompanyName)
);INSERT #StateCompany (StateCode, CompanyName) VALUES
('AL', 'Liberty Comp.'),
('AL', 'Reg Produce'),
('AZ', 'Amerifresh'),
('AZ', 'Coosemans phonex'),
('AZ', 'Crown Jewls'),
('CA', 'Organic Farms'),
('CA', 'Albert''s Organics'),
('CA', 'Awe Sum'),
('CA', 'Bay City Produce'),
('CA', 'Crown Jewls')
END;--======================================================================
DECLARE
@_sql NVARCHAR(4000) = N'',
@_join_list NVARCHAR(4000) = N'',
@_first_state CHAR(2) = NULL,
@_prev_state CHAR(2) = NULL,
@_select_list NVARCHAR(4000) = N'',
@_debug BIT = 0;SELECT TOP (100)
@_join_list = CONCAT(@_join_list, N'
', CASE WHEN @_prev_state IS NULL THEN '' ELSE N' LEFT JOIN ' END,
N'(SELECT sc.StateCode, sc.CompanyName, Row_Num = ROW_NUMBER() OVER (ORDER BY sc.CompanyName) FROM #StateCompany sc WHERE sc.StateCode = ''', sc.StateCode, N''') [', sc.StateCode, N']', N'
ON [' + @_prev_state + N'].Row_Num = [' + sc.StateCode + N'].Row_Num'),
@_prev_state = sc.StateCode,
@_first_state = ISNULL(@_first_state, sc.StateCode)
FROM
#StateCompany sc
GROUP BY
sc.StateCode
ORDER BY
COUNT(1) DESC;--PRINT(@_join_list);
SELECT TOP (100)
@_select_list = CONCAT(@_select_list, N',
[', s.StateName, N'] = [', s.StateCode, '].CompanyName')
FROM
#States s
ORDER BY
s.StateName ASC;--PRINT (@_select_list);
SET @_sql = CONCAT(N'
SELECT
', STUFF(@_select_list, 1, 4, ''), N'
FROM ', @_join_list, N'
ORDER BY
[', @_first_state, '].Row_Num');IF @_debug = 1
BEGIN
PRINT(@_sql);
END;
ELSE
BEGIN
EXEC sys.sp_executesql @_sql;
END;Results...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply