Prev: Filtering and adding rows based on some condition
Next: retrieving connection string w/ ConfigurationManager
From: Rich on 19 May 2010 15:33
I have to populate a list (vertically) for a report (in Reporting Services)
with content from a single row (the selected row) of a (non-normalized)
table. Some of the fields from the selected row may be empty. I have to
exclude these fields from the list.

I was originally hardcoding the fields in the Report list (stacking
textboxes vertically and referencing the respective field from the report
dataset), but this was leaving gaps in the list (showing empty fields) if not
all the fields contained a value. So I decided to transpose the row data of
the selected row from a horizontal orientation to a vertical orientation
(where each field is its own row) by adding each field value to a table var
which only contains one field (one column) and then selecting only the rows
from that table var where the field was not empty. The following snippet
shows how I do this, but I want to know if this can be done without using a
table var (or #tmp table). And I need to exclude the empty fields:

declare @s1 table(Item varchar(400))

insert into @s1 select top 1 contact from tbl1 where subs = 14360 order by
invoice
insert into @s1 select top 1 title from tbl1 where subs = 14360 order by
invoice
insert into @s1 select top 1 firm from tbl1 where subs = 14360 order by
invoice
insert into @s1 select top 1 address from tbl1 where subs = 14360 order by
invoice
insert into @s1 select top 1 city + ', ' + state + ' ' + zip from tbl1
where subs = 14360 order by invoice
insert into @s1 select top 1 subs from tbl1 where subs = 14360 order by
invoice

select * from @s1 where len(item) > 0

My goal is to eliminate the Insert Into statement so that I don't have to
stuff this into a stored procedure. How to do this (sql server 2000 or
2005)?

Thanks
From: John Bell on 19 May 2010 17:23
On Wed, 19 May 2010 12:33:01 -0700, Rich
<Rich(a)discussions.microsoft.com> wrote:

>I have to populate a list (vertically) for a report (in Reporting Services)
>with content from a single row (the selected row) of a (non-normalized)
>table. Some of the fields from the selected row may be empty. I have to
>exclude these fields from the list.
>
>I was originally hardcoding the fields in the Report list (stacking
>textboxes vertically and referencing the respective field from the report
>dataset), but this was leaving gaps in the list (showing empty fields) if not
>all the fields contained a value. So I decided to transpose the row data of
>the selected row from a horizontal orientation to a vertical orientation
>(where each field is its own row) by adding each field value to a table var
>which only contains one field (one column) and then selecting only the rows
>from that table var where the field was not empty. The following snippet
>shows how I do this, but I want to know if this can be done without using a
>table var (or #tmp table). And I need to exclude the empty fields:
>
>declare @s1 table(Item varchar(400))
>
>insert into @s1 select top 1 contact from tbl1 where subs = 14360 order by
>invoice
>insert into @s1 select top 1 title from tbl1 where subs = 14360 order by
>invoice
>insert into @s1 select top 1 firm from tbl1 where subs = 14360 order by
>invoice
>insert into @s1 select top 1 address from tbl1 where subs = 14360 order by
>invoice
>insert into @s1 select top 1 city + ', ' + state + ' ' + zip from tbl1
>where subs = 14360 order by invoice
>insert into @s1 select top 1 subs from tbl1 where subs = 14360 order by
>invoice
>
>select * from @s1 where len(item) > 0
>
>My goal is to eliminate the Insert Into statement so that I don't have to
>stuff this into a stored procedure. How to do this (sql server 2000 or
>2005)?
>
>Thanks
Hi

You could do something similar to:

select top 1 CAST(contact as varchar(400)) AS item from tbl1 where
subs = 14360 and NULLIF(contact,'') IS NOT NULL order by invoice
UNION ALL select top 1 CAST(title as varchar(400)) from tbl1 where
subs = 14360 and NULLIF(title,'') IS NOT NULL order by invoice
UNION ALL select top 1 CAST(firm as varchar(400)) from tbl1 where
subs = 14360 and NULLIF(firm,'') IS NOT NULL order by invoice
UNION ALL select top 1 CAST(address as varchar(400)) from tbl1 where
subs = 14360 and NULLIF(address,'') IS NOT NULL order by invoice
UNION ALL select top 1 CAST(city + ', ' + state + ' ' + zip as
varchar(400)) from tbl1
where subs = 14360 and NULLIF(city + ', ' + state + ' ' + zip,'') IS
NOT NULL order by invoice
UNION ALL select top 1 CAST(subs as varchar(400)) from tbl1 where
subs = 14360 and NULLIF(subs,'') IS NOT NULL order by invoice

John
From: Plamen Ratchev on 19 May 2010 23:13
On SQL Server 2005 you can use UNPIVOT:

SELECT item
FROM (
SELECT TOP (1) CAST(contact AS VARCHAR(400)) AS contact,
CAST(title AS VARCHAR(400)) AS title,
CAST(firm AS VARCHAR(400)) AS firm,
CAST(address AS VARCHAR(400)) AS address,
CAST(city + ', ' + state + ' ' + zip AS VARCHAR(400))
AS address2,
CAST(subs AS VARCHAR(400)) AS subs
FROM tbl1
WHERE subs = 14360
ORDER BY invoice) AS T
UNPIVOT
(item FOR col IN (contact, title, firm, address, address2, subs)) AS
U;

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Filtering and adding rows based on some condition
Next: retrieving connection string w/ ConfigurationManager