Grouping and Exporting to Text File
-
Hello,
I have very simple data from a SQL table that needs grouped and formatted like below. Two columns, ID and Location, separated by 5 hyphens between IDs. I tried the Table but I can't get all the data to be stacked on top of each other. Is this possible?
SQL table
Desired Output
-
sorry - misread and though you after some SQL - on SSRS I don't know
-
Really, I am just looking for a way to automate this whether it be through SQL, SSIS, or SSRS. Any ideas are appreciated.
- frederico_fonseca - Friday, June 8, 2018 12:12 PM
Really, I am just looking for a way to automate this whether it be through SQL, SSIS, or SSRS. Any ideas are appreciated.
-
SQL then
declare @Table table
( id int
, location varchar(50)
)
insert into @Table select 1 , 'west'
insert into @Table select 1 , 'east'
insert into @Table select 1 , 'north'
insert into @Table select 2 , 'west'
insert into @Table select 2 , 'east'
insert into @Table select 3 , 'north'
insert into @Table select 3 , 'south';
with groups
as
(select distinct t.*
from @Table
outer apply (select id , 1
union
select id , 3
) t (id, order1)
)select location
from (select t1.id
, 2 as order1
, case
when location = 'west' then 1
when location = 'east' then 2
when location = 'north' then 3
when location = 'south' then 4
end as order2
, t1.location
from @Table t1
union all
select id
, order1
, 0 as order2
, case
when order1 = 1
then convert(varchar(50), id)
else '-----'
end
from groups) t
order by id
, order1
, order2 - frederico_fonseca - Friday, June 8, 2018 12:39 PM
That's awesome. My only problem is that my table has thousands of IDs and is growing. I need something that is dynamic.
-
And why you think it wont work? or is it that the locations is more than those you mentioned?
And in any case why do you need it on that format?
-
I can think of a half dozen reasons it may not work, but the bigger problem is that the poster's source table has no inherent order. There's nothing about a SQL Server table that guarantees the order in which rows appear except for ORDER BY. There's no data in the rows to say that a given value for a given ID should appear in any particular location within that ID's list of values. Failing that, federico's query does a great job providing that order, but only for the limited list of values, whereas reality might well have many thousands of different values. Until those values actually have an "ORDER" within the actual data, there's quite literally NOTHING that will do that job without knowing all the possible values in advance and what order they should appear in.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Here's the test data setup I used...
CREATE TABLE #TestTable
(
id INT
,location VARCHAR(50)
)
;
INSERT INTO #TestTable
(id,location)
VALUES (1,'west')
,(1,'east')
,(1,'north')
,(2,'west')
,(2,'east')
,(3,'north')
,(3,'south')
;
If the location of the first and last dashed lines isn't important, then this problem can be pretty simple. Run this code with the "Text Results" window active to see the text results.
SET NOCOUNT ON;
SELECT location = ISNULL(location,'----------'+CHAR(13)+CHAR(10)+CONVERT(VARCHAR(50),id))
FROM #TestTable
GROUP BY id,location WITH ROLLUP
HAVING GROUPING(id) = 0
ORDER BY id,location
;
Here's the output:
location
--------------------------------------------------
----------
1
east
north
west
----------
2
east
west
----------
3
north
south
As Steve said, if you want the original order of the locations, then you'll have to do something like add a column to the table that preserves that order.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Saturday, June 16, 2018 5:14 PM
Thanks Jeff, this worked for what I needed!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply