Prev: Collect csv from ftp server and bulk import
Next: Link server Identity column insert question?
From: deeg on 30 Jun 2010 14:18
We have a distinct list of email addresses in alpha order and we need
to transform it from a single column into a grid of three columns
maintaining the alpha order. The list is contained in a temp table
inside of our query. We then use the temp table list and perform case
statement with a mod on the row_number in a select statement to
columnze the data. However, the columnar data contains a null value in
two of the three columns and we are needing to remove the nulls and
have the actual values on each row in the output.

Here is our current sql:

CREATE TABLE #tmpTable
( Email_Address VARCHAR(50)
)

INSERT INTO #tmpTable
SELECT DISTINCT EMAIL_ADDRESS
FROM VisitorEmail
ORDER BY EMAIL_ADDRESS

SELECT CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 1
THEN EMAIL_ADDRESS END AS EMAIL1,
CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 2
THEN EMAIL_ADDRESS END AS EMAIL2,
CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 0
THEN EMAIL_ADDRESS END AS EMAIL3
FROM #tmpTable
From: Dom on 30 Jun 2010 14:25
On Jun 30, 2:18 pm, deeg <deeg0...(a)gmail.com> wrote:
> We have a distinct list of email addresses in alpha order and we need
> to transform it from a single column into a grid of three columns
> maintaining the alpha order. The list is contained in a temp table
> inside of our query. We then use the temp table list and perform case
> statement with a mod on the row_number in a select statement to
> columnze the data. However, the columnar data contains a null value in
> two of the three columns and we are needing to remove the nulls and
> have the actual values on each row in the output.
>
> Here is our current sql:
>
> CREATE  TABLE   #tmpTable
> (               Email_Address   VARCHAR(50)
> )
>
> INSERT  INTO    #tmpTable
> SELECT  DISTINCT        EMAIL_ADDRESS
> FROM    VisitorEmail
> ORDER BY EMAIL_ADDRESS
>
> SELECT  CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 1
>                         THEN EMAIL_ADDRESS END AS EMAIL1,
>         CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 2
>                         THEN EMAIL_ADDRESS END AS EMAIL2,
>         CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 0
>                         THEN EMAIL_ADDRESS END AS EMAIL3
> FROM    #tmpTable


>However, the columnar data contains a null value in
> two of the three columns and we are needing to remove the nulls and
> have the actual values on each row in the output.

I don't know what you mean by "actual values", but off-hand I think
you just need the "ELSE" part of the case statement. Eg,

CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 0
THEN EMAIL_ADDRESS
ELSE <actual-value>
END AS EMAIL3


From: deeg on 30 Jun 2010 14:32
On Jun 30, 1:25 pm, Dom <dolivas...(a)gmail.com> wrote:
> On Jun 30, 2:18 pm, deeg <deeg0...(a)gmail.com> wrote:
>
>
>
>
>
> > We have a distinct list of email addresses in alpha order and we need
> > to transform it from a single column into a grid of three columns
> > maintaining the alpha order. The list is contained in a temp table
> > inside of our query. We then use the temp table list and perform case
> > statement with a mod on the row_number in a select statement to
> > columnze the data. However, the columnar data contains a null value in
> > two of the three columns and we are needing to remove the nulls and
> > have the actual values on each row in the output.
>
> > Here is our current sql:
>
> > CREATE  TABLE   #tmpTable
> > (               Email_Address   VARCHAR(50)
> > )
>
> > INSERT  INTO    #tmpTable
> > SELECT  DISTINCT        EMAIL_ADDRESS
> > FROM    VisitorEmail
> > ORDER BY EMAIL_ADDRESS
>
> > SELECT  CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 1
> >                         THEN EMAIL_ADDRESS END AS EMAIL1,
> >         CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 2
> >                         THEN EMAIL_ADDRESS END AS EMAIL2,
> >         CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 0
> >                         THEN EMAIL_ADDRESS END AS EMAIL3
> > FROM    #tmpTable
> >However, the columnar data contains a null value in
> > two of the three columns and we are needing to remove the nulls and
> > have the actual values on each row in the output.
>
> I don't know what you mean by "actual values", but off-hand I think
> you just need the "ELSE" part of the case statement.  Eg,
>
> CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 0
>                           THEN EMAIL_ADDRESS
>                            ELSE <actual-value>
>                            END AS EMAIL3- Hide quoted text -
>
> - Show quoted text -

The actual value mentioned is the email address. Here is a sample of
the return data:

Email1 Email2 Email3
Address null null
null Address null
null null Address

This is repeated for all rows in the data. We need the above three
rows reduced to one row in this format:

Email1 Email2 Email3
Address Address Address

Thus, we need to get the null values out of the datarows and move the
actual addresses up.
From: Dom on 30 Jun 2010 14:50
On Jun 30, 2:32 pm, deeg <deeg0...(a)gmail.com> wrote:
> On Jun 30, 1:25 pm, Dom <dolivas...(a)gmail.com> wrote:
>
>
>
>
>
> > On Jun 30, 2:18 pm, deeg <deeg0...(a)gmail.com> wrote:
>
> > > We have a distinct list of email addresses in alpha order and we need
> > > to transform it from a single column into a grid of three columns
> > > maintaining the alpha order. The list is contained in a temp table
> > > inside of our query. We then use the temp table list and perform case
> > > statement with a mod on the row_number in a select statement to
> > > columnze the data. However, the columnar data contains a null value in
> > > two of the three columns and we are needing to remove the nulls and
> > > have the actual values on each row in the output.
>
> > > Here is our current sql:
>
> > > CREATE  TABLE   #tmpTable
> > > (               Email_Address   VARCHAR(50)
> > > )
>
> > > INSERT  INTO    #tmpTable
> > > SELECT  DISTINCT        EMAIL_ADDRESS
> > > FROM    VisitorEmail
> > > ORDER BY EMAIL_ADDRESS
>
> > > SELECT  CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 1
> > >                         THEN EMAIL_ADDRESS END AS EMAIL1,
> > >         CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 2
> > >                         THEN EMAIL_ADDRESS END AS EMAIL2,
> > >         CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 0
> > >                         THEN EMAIL_ADDRESS END AS EMAIL3
> > > FROM    #tmpTable
> > >However, the columnar data contains a null value in
> > > two of the three columns and we are needing to remove the nulls and
> > > have the actual values on each row in the output.
>
> > I don't know what you mean by "actual values", but off-hand I think
> > you just need the "ELSE" part of the case statement.  Eg,
>
> > CASE WHEN (ROW_NUMBER() OVER (ORDER BY EMAIL_ADDRESS) % 3) = 0
> >                           THEN EMAIL_ADDRESS
> >                            ELSE <actual-value>
> >                            END AS EMAIL3- Hide quoted text -
>
> > - Show quoted text -
>
> The actual value mentioned is the email address. Here is a sample of
> the return data:
>
> Email1          Email2          Email3
> Address        null               null
> null               Address        null
> null               null               Address
>
> This is repeated for all rows in the data. We need the above three
> rows reduced to one row in this format:
>
> Email1          Email2          Email3
> Address        Address        Address
>
> Thus, we need to get the null values out of the datarows and move the
> actual addresses up.- Hide quoted text -
>
> - Show quoted text -

Sorry I misunderstood the first time. My first answer is of no use.
I think you need to do a cross-join on the #tmpTable three times.
CAUTION: If tmpTable is big, this could really hurt. First put an
autonumber in the tmpTable:

CREATE TABLE #tmpTable
( Email_Address VARCHAR(50)
ID identity
)


INSERT INTO #tmpTable
SELECT DISTINCT EMAIL_ADDRESS
FROM VisitorEmail
ORDER BY EMAIL_ADDRESS


Then cross join the table three times, and select when the three
identities do not match

Select t1.email_address, t2,email_address, t3.email_address
From #tmpTable t1
cross join #tmpTable t2
cross join #tmpTable t3
where t1.ID <> t2.ID and t1.ID <> t3.ID and t2.ID <> t3.ID

I'm pretty sure that will work. Again, it can be very time-consuming.

Dom
From: --CELKO-- on 30 Jun 2010 15:03
SELECT user_id,
MAX (CASE WHEN (ROW_NUMBER() OVER (ORDER BY email_address) % 3)
= 1
THEN email_address ELSE NULL END) AS email_1,
MAX (CASE WHEN (ROW_NUMBER() OVER (ORDER BY email_address) % 3)
= 2
THEN email_address ELSE NULL END) AS email_2,
MAX (CASE WHEN (ROW_NUMBER() OVER (ORDER BY email_address) % 3)
= 0
THEN email_address ELSE NULL END) AS email_3
FROM #TmpTable
GROUP BY user_id;
 |  Next  |  Last
Pages: 1 2
Prev: Collect csv from ftp server and bulk import
Next: Link server Identity column insert question?