Unpivot query

Question

Unpivot query

as_1234

SSCrazy

Points: 2865
More actions
February 19, 2019 at 9:57 am

#416705

All,
DDL and DML:
CREATE TABLE [dbo].[tabDemo](
[ProductGroup] [varchar](50) NULL,
[ContactType] [varchar](50) NULL
) ON [PRIMARY]
GO
insert
into tabDemo(ProductGroup,ContactType) values ('pg1','ct1')
Insert into tabDemo(ProductGroup,ContactType) values ('pg2','ct2')
insert into tabDemo(ProductGroup,ContactType) values ('pg3','ct3')
insert into tabDemo(ProductGroup,ContactType) values ('pg4','ct4')
I currently have the following query:
select data from (
select productgroup, contacttypefrom tabdemowhere ProductGroup='pg1'
) as cp
unpivot
(
data for headings in (productgroup, contacttype )
) as up;
Which gives me a column of results as follows:
pg1 cp1
What I would like is an output of:
Product group | pg1 Contact type | cp1
(Two columns per row)
With product group and contact types being static values. Is it possible? I wondered about something with a case statement but wasn't sure how to start putting it together.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Something like this could work if I understood correctly.
SELECT u.ColumnName, u.ColumnValue FROM tabDemo CROSS APPLY (VALUES( 'Product Group', ProductGroup), ('Contact Type', ContactType)) AS u( ColumnName, ColumnValue);
This is explained in here: http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

as_1234 SSCrazy Points: 2865 More actions · Answer 2

Hello,

Thank you. That gives me the result I need.

Thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

as1981 - Tuesday, February 19, 2019 12:49 PM
Hello,
Thank you. That gives me the result I need.
Thanks

That's good to know, but do you understand the solution?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

as_1234 SSCrazy Points: 2865 More actions · Answer 4

I'm still looking at it but possibly something like this?:

It creates a temporary as follows:

It takes the first row from tabDemo and the first set in the values list. If the item in the values list is not in quotes it takes the value from the matching column in tabDemo otherwise it takes it as a literal value.

In then takes the first row from tabDemo and the second set in the values list and does the same thing.

Next the second row from tabDemo and the first set in the values list.

It seems to be a kind of cartesian between the tabDemo table and the values list but with replacement?

Thanks