Solved

SQL Select Query problems

Posted on 2016-10-11
10
50 Views
Last Modified: 2016-10-15
Hello EE,

in a table I got columns values Code, Desc01, Desc02, Desc03, Desc04 up to 15

in another table, I got columns:   Code, Qty01, Qty02, Qty03, Qty04 ... all to Qty15.

my question is, I need to read the second table but I dont want to show in headers Qty01 Qty02 ..
I want to show the real description based on the "Code value"

if code is R1
Desc01 to 15 will be let's say:   XXS, XS, S, M, L, XL, XXL, XXXL

if R2 it could be:   0 2 4 6 8 10 12 14 16

i want to show the good QTYS on the real description, without showing "Qty01" "Qty02"  you know what I mean ?

can you help on this,.. ?
0
Comment
Question by:PhilippeRenaud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41838316
SELECT T1.*
FROM Table1 T1
    INNER JOINT Table2 T2 ON T1.Code = T2.Code

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838323
if I do this, I will get columns name as  QTy01, Qty02 Qty03

this is not what I want
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41838324
No, it's just bringing columns from Table1 (T1).Qtys are from Table2 (T2).
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838357
what do you want the output to show?
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838370
If in my, lets call is TableA, I have :

Code    DEsc01   Desc02    Desc03    Desc04
R1         XXS          XS            S              M
R2          0             2               4              6


and in TableB I Got :

Code      QTY01       QTY02        QTY03        QTY04
R1           5                 0                3                  1
R2           1                 6                3                  10


if I do a query that I want to know the Qtys for R1 i woudl like the results to be like this :

XXS      XS       S       M
5          0          3      1
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838382
if you want XXS, XS, ... to be the header, you will need to create a dynamic query.

if you want XXS, XS, ... to be the first row, this is much easier:
select DEsc01,  Desc02,    Desc03,    Desc04, ....
from tableA
where Code = 'R1'
union all
select QTY01,  QTY02,    QTY03,    QTY04, ....
from tableB
where Code = 'R1'

Open in new window




So what do you want?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41838546
@Author - You want below as column Names?
XXS      XS       S       M
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41839003
yes !
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41839619
Here !! Pls  try..

Table Creation

--

CREATE TABLE table1
(
	 Code  VARCHAR(10)
	,DEsc01  VARCHAR(10) 
	,Desc02   VARCHAR(10) 
	,Desc03   VARCHAR(10) 
	,Desc04 VARCHAR(10)
)
GO

INSERT INTO table1 VALUES
('R1'     ,    'XXS' ,         'XS',            'S',              'M'),
('R2'      ,    '0'      ,       '2'      ,         '4',              '6')


CREATE TABLE table2
(
	 Code  VARCHAR(10)
	,QTY01  INT 
	,QTY02   INT
	,QTY03   INT
	,QTY04 INT
)
GO

INSERT INTO table2 VALUES
('R1'     ,      5 ,                0,                3,                  1),
('R2'     ,      1  ,               6 ,               3 ,                 10)
GO

--

Open in new window


Solution

--


DECLARE @Code AS VARCHAR(10) = 'R1'
DECLARE @Cols AS VARCHAR(1000) = ''

SELECT @Cols = @Cols + '[' + Desc01 + '],[' + Desc02  + '],[' + Desc03 + '],[' + Desc04 + ']' FROM table1 
WHERE Code = @Code

DECLARE @Query AS VARCHAR(MAX) = ''

SET @Query = '

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY code ORDER BY(SELECT 1)) rnk FROM 
	(
		SELECT *  FROM table1			
	)p
	WHERE code = ' + '''' + @code + '''' + ' 
)
SELECT ' +  @Cols +  ' FROM 
(
	SELECT *
	FROM
	(
		SELECT * FROM CTE c 
	)t
	UNPIVOT
	(
		rnku FOR t IN ( Desc01 , Desc02  , Desc03 , Desc04 )
	) AS Unvot
)p PIVOT
(
	MAX(t) FOR rnku IN ( ' +  @Cols  + ' )
)as pvt
WHERE rnk <> 1
UNION ALL
SELECT QTY01, QTY02 , QTY03 , QTY04 FROM table2 
WHERE Code = ' + '''' + @code + ''''

EXEC (@Query)


--

Open in new window



Output for Code = R1

XXS      XS      S      M
5      0      3      1



Output for Code = R2

0      2      4      6
1      6      3      10


Hope it helps !!
0
 
LVL 1

Author Closing Comment

by:PhilippeRenaud
ID: 41845008
thanks
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display Date and Time 7 48
SQL Server 2012 to SQL Server 2016 24 52
Error "Could not load mobile report" in SQL SSRS 2016 after SP1 CU2 Update 2 34
T-SQL Query 9 30
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

738 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