Display column 2 horizontally and matched with column 1.
-
The table on the right is what SQL gives me when I run my JOIN query (Column 1, Location and Column 2, Representative are from 2 different tables). I am hoping that I can somehow display the data as seen on the right. The catch is that the number of representatives will differ on a monthly basis (that's how often I have to run this report) and the number of locations will differ as well. This is just a snapshot, but there are nearly 400 records in the table on the left as of now, and each location can have anywhere from 1 to 5 reps.
I'm pretty new to SQL, but after some digging around I found out that there's a Pivot function that should help me in this regard, but I couldn't get it to work so any help would be greatly appreciated.
-
Do you have to do this in T-SQL? Could you, instead, for example, use Reporting Services or Excel to do your Pivot for you?
T-SQL can Pivot, but you have to declare each column. If you're going to have a varying column number, you have to build dynamic SQL to achieve this.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, March 28, 2017 7:25 AM
The data is compiled in Excel right now. I pull a list for locations, and a list for reps (through the company's CRM software). Then I compile the 2 together in a third worksheet in Excel. It all takes about 2 hours. I was hoping to get that number down though, and have SQL spit out exactly what I need and then just copy+paste it into a worksheet and send it off to the higher ups.
-
So the data isn't already in SQL? Do you not use a Pivot table in Excel already then? These are generally pretty quick, and the direction my thought was going anywa. Dynamic pivoting is generally best to do in your presentation layer, not SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - peter.godzina - Tuesday, March 28, 2017 7:33 AM
How about something like this, where the query does the work, and you just copy and paste the grid values out of SSMS into Excel?
WITH REPS_ADDRESSES AS (SELECT Representative, Location
FROM (
VALUES ('Darius T', '101-103 Orenda Road #1'),
('Diana H', '101-103 Orenda Road #1'),
('Randy H', '10136 201 Street #Unit A'),
('Malcolm E', '10136 201 Street #Unit A'),
('Randy H', '10136 201 Street #Unit B'),
('Malcolm E', '10136 201 Street #Unit B'),
('Matt S', '1055 Vernon Drive #1')
) AS X (Representative, Location)
),
LOCATIONS AS (SELECT Location, COUNT(DISTINCT Representative) AS REP_COUNT,
ROW_NUMBER() OVER(ORDER BY Location) AS SORT_VALUE
FROM REPS_ADDRESSES AS RA
GROUP BY Location
),
MAX_COUNT AS (SELECT MAX(REP_COUNT) AS MAX_REPS
FROM LOCATIONS
),
TOP_LOCATION AS (SELECT TOP (1) L.Location, C.MAX_REPS
FROM LOCATIONS AS L
INNER JOIN MAX_COUNT AS C
ON L.REP_COUNT = C.MAX_REPS
ORDER BY L.Location
),
NUMBERED_REPS AS (SELECT RA.Location, RA.Representative, TL.MAX_REPS,
'Rep' + CAST(ROW_NUMBER() OVER (PARTITION BY RA.Location ORDER BY RA.Representative) AS varchar(2)) AS FIELD_NAME
FROM REPS_ADDRESSES AS RA
INNER JOIN TOP_LOCATION AS TL
ON RA.Location = TL.Location
),
REPS_FIELD_LIST AS (SELECT 0 AS SORT_VALUE,
'Location' + (
SELECT TOP(100) CHAR(9) + NR.FIELD_NAME
FROM NUMBERED_REPS AS NR
FOR XML PATH('')
) AS FIELD_LIST
)
SELECT OUTPUT_RECORD
FROM (
SELECT SORT_VALUE, FIELD_LIST AS OUTPUT_RECORD
FROM REPS_FIELD_LIST
UNION ALL
SELECT L.SORT_VALUE, L.Location + X.REP_LIST AS OUTPUT_RECORD
FROM LOCATIONS AS L
CROSS APPLY (
SELECT CHAR(9) + R.Representative
FROM REPS_ADDRESSES AS R
WHERE R.Location = L.Location
FOR XML PATH('')
) AS X (REP_LIST)
) AS X
ORDER BY SORT_VALUE;Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - Thom A - Tuesday, March 28, 2017 7:51 AM
No, it is in SQL. Up until now I could only access the data through MS Dynamics though. But now I can access the database through SQL Management Studio, which is a lot faster when it comes to extracting data from the database.
And I do not use Pivot tables in Excel for this. The entire finished table has A LOT more columns than just what I have up there (17 columns from the "Locations" table). I don't think a pivot table would be able to handle all that info. But what I want to do in SQL, is pull all 18 columns from the locations table, and then JOIN the Representatives table, but have the info added horizontally, so I don't have the same location listed 3 or 4 or 5 times (depending on how many contacts are attached to that particular location).
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply