Converting 2 columns into 4 woes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting 2 columns into 4 woes

Author

Topic

Marshy100
Starting Member

3 Posts

Posted - 2012-05-10 : 13:53:53

Hi All!
I have a linked server connected to an ODBC data source which gives me RAW data from a SCADA system. The table has two colums the TAG name an it's actual value.
I'd like to be a ble to query the table to produce a grid similar to whats shown below:

TAG VALUE
A_DB33_MIXID NULL
A_DB33_JOBID NULL
A_DB33_CURR NULL
A_DB33_TOT NULL
A_DB34_MIXID NULL
A_DB34_JOBID NULL
A_DB34_CURR NULL

REQ TABLE

MIXID JOBID BATCHS CURRENT
A_DB33_MIXID A_DB33_JOBID A_DB33_TOT A_DB33_CURR
A_DB34_MIXID A_DB34_JOBID A_DB34_TOT A_DB35_CURR

I've used queries such as:
select TAG from TABLE where TAG LIKE '%MIXID'
select TAG from TABLE where TAG LIKE '%JOBID'

to get the individual columns, its how I go about combining them to make a table I'm struggling with.

Any help would be appreciated.

Thanks

Rich

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 14:44:20

quote:
Originally posted by Marshy100

Hi All!
I have a linked server connected to an ODBC data source which gives me RAW data from a SCADA system. The table has two colums the TAG name an it's actual value.
I'd like to be a ble to query the table to produce a grid similar to whats shown below:

TAG VALUE
A_DB33_MIXID NULL
A_DB33_JOBID NULL
A_DB33_CURR NULL
A_DB33_TOT NULL
A_DB34_MIXID NULL
A_DB34_JOBID NULL
A_DB34_CURR NULL

REQ TABLE

MIXID JOBID BATCHS CURRENT
A_DB33_MIXID A_DB33_JOBID A_DB33_TOT A_DB33_CURR
A_DB34_MIXID A_DB34_JOBID A_DB34_TOT A_DB35_CURR

I've used queries such as:
select TAG from TABLE where TAG LIKE '%MIXID'
select TAG from TABLE where TAG LIKE '%JOBID'

to get the individual columns, its how I go about combining them to make a table I'm struggling with.

Any help would be appreciated.

Thanks

Rich


select TAG,NULL AS Value from TABLE where TAG LIKE '%MIXID'
UNION ALL
select TAG,NULL from TABLE where TAG LIKE '%JOBID'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Marshy100
Starting Member

3 Posts

Posted - 2012-05-10 : 15:05:52

quote:


select TAG,NULL AS Value from TABLE where TAG LIKE '%MIXID'
UNION ALL
select TAG,NULL from TABLE where TAG LIKE '%JOBID'

Thanks for the reply. I tried your suggestion but it puts
A_DB3x_MIXID and A_DB3x_JOBID in the same column?

I've tried to make the tables more clear:

TAG,____________VALUE
A_DB33_MIXID,___NULL
A_DB33_JOBID,___NULL
A_DB33_CURR,____NULL
A_DB33_TOT,_____NULL
A_DB34_MIXID,___NULL
A_DB34_JOBID,___NULL
A_DB34_CURR,____NULL

REQ TABLE

MIXID,__________ JOBID,_________BATCHS,_________ CURRENT
A_DB33_MIXID,____A_DB33_JOBID,__A_DB33_TOT,______A_DB33_CURR
A_DB34_MIXID,____A_DB34_JOBID,__A_DB34_TOT,______A_DB35_CURR

The Value Column can be ignored for now. The data in the TAG column never changes and is always in the same order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 15:33:02

[code]
SELECT MAX(CASE WHEN Category='MIXID' THEN TAG END) AS MIXID,
MAX(CASE WHEN Category='JOBID' THEN TAG END) AS JOBID,
MAX(CASE WHEN Category='TOT' THEN TAG END) AS BATCHS,
MAX(CASE WHEN Category='CURR' THEN TAG END) AS CURRENT
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LEFT(REVERSE(TAG),CHARINDEX('_',REVERSE(TAG))-1) ORDER BY TAG) AS Rn,*,REVERSE(LEFT(REVERSE(TAG),CHARINDEX('_',REVERSE(TAG))-1)) AS Category
FROM YourTable
)t
GROUP BY Rn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Marshy100
Starting Member

3 Posts

Posted - 2012-05-10 : 15:45:38

quote:

Originally posted by visakh16


SELECT MAX(CASE WHEN Category='MIXID' THEN TAG END) AS MIXID,
MAX(CASE WHEN Category='JOBID' THEN TAG END) AS JOBID,
MAX(CASE WHEN Category='TOT' THEN TAG END) AS BATCHS,
MAX(CASE WHEN Category='CURR' THEN TAG END) AS CURRENT
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LEFT(REVERSE(TAG),CHARINDEX('_',REVERSE(TAG))-1) ORDER BY TAG) AS Rn,*,REVERSE(LEFT(REVERSE(TAG),CHARINDEX('_',REVERSE(TAG))-1)) AS Category
FROM YourTable
)t
GROUP BY Rn

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks I'll give it a go!
I was just coming back to say I sort of found a way:


select a.tag as JOBID , b.tag as MIXID, c.tag as BATCHS, d.tag as CURENT
from(
select ROW_NUMBER() OVER(ORDER BY tag) AS RowNo,tag from Table_1 where tag like '%jobid') a,
(select ROW_NUMBER() OVER(ORDER BY tag) AS RowNo,tag from Table_1 where tag like '%mixid') b,
(select ROW_NUMBER() OVER(ORDER BY tag) AS RowNo,tag from Table_1 where tag like '%tot%') c,
(select ROW_NUMBER() OVER(ORDER BY tag) AS RowNo,tag from Table_1 where tag like '%curr%') d
where a.RowNo = b.RowNo AND b.RowNo = c.RowNo AND c.RowNo = d.RowNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources