Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using TSQL to Return Column Data as Rows

Posted on 2016-10-04
3
Medium Priority
?
86 Views
Last Modified: 2016-10-05
Thank you all in advance!

I have table: TableA

RowID Greeting  IDINS  VerifyApt
108            y            y              y
109            y            n              y
110            y          OOS          y

I would like to create a new table, TableB from the data in TableA that looks like:

ROWID        Step           DATA
108          Greeting            y
108             IDNS               y
108         VerifApt              y
109         Greeting             y
109             IDNS                n
109          VerifyApt            y
110          Greeting            y
110              IDNS               y
110         VerifyApt            y


Is this a PIVOT? UNPIVOT? The SQL I found kept referring to an aggregate function but as you can see, I dont need one.

Thank you all again.
0
Comment
Question by:Victor Nares
3 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 41828452
I'd use CROSS APPLY for this:

SELECT
    RowID,
    which_column AS Step,
    CASE which_column
    WHEN 'Greeting' THEN Greeting
    WHEN 'IDINS' THEN IDINS
    WHEN 'VerifyApt' THEN VerifyApt
    ELSE '?'
    END AS DATA
FROM /*your_table_name*/ (
    VALUES(108,            'y',            'y',              'y'),
    (109 ,           'y' ,           'n',              'y'),
    (110,            'y'  ,        'OOS',          'y')
) AS your_sample_data(RowID, Greeting, IDINS, VerifyApt)
CROSS APPLY (
    VALUES('Greeting'),('IDINS'),('VerifyApt')
) AS which_columns(which_column)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 41828540
try UNPIVOT
declare @table table (RowID int, Greeting varchar(1), IDINS varchar(1), VerifyApt varchar(1))
insert @table values (108,'y','y','y'),(109,'y','n','y'),(110,'y','y','y')

select RowID, Step, DATA
  from @table
unpivot (DATA for Step IN (Greeting,IDINS,VerifyApt)) p
/*
RowID	Step	DATA
108	Greeting	y
108	IDINS	y
108	VerifyApt	y
109	Greeting	y
109	IDINS	n
109	VerifyApt	y
110	Greeting	y
110	IDINS	y
110	VerifyApt	y
*/

Open in new window

1
 

Author Closing Comment

by:Victor Nares
ID: 41830185
This worked Great!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question